В ячейке B5 у меня есть формула:
=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&"")-(COUNTIF(A2:A10,A2:A10&"")=1))
Если столбец A содержит данные, как показано ниже, формула возвращает правильный ответ 3, поскольку есть три значения, которые встречаются более одного раза (14, 16 и 17). Это именно то, что я хочу знать; есть три значения, которые встречаются более одного раза. Мне не нужно знать, что это за значения, и где они находятся (хотя у меня есть отличная штука для этой последней части).
Column A:
12
13
14
14
14
15
16
16
17
17
Однако если где-либо в диапазоне A2:A10 находится пустая ячейка, то количество повторяющихся значений уменьшается на 1 (в примере выше формула вернет 2, хотя должно быть 3). В моем примере ниже пустая ячейка представлена как «B».
Column A:
12
13
14
B
14
15
16
16
17
17
В этом примере числа 14, 16 и 17 встречаются более одного раза, поэтому формула должна возвращать 3, но возвращает 2.
Я почти уверен, что это из-за первой части =SUMPRODUCT((A2:A10<>""), где он подсчитывает по сути непустые ячейки. Конечно, если там окажется две (или больше) пустых ячеек, то он снова увеличится, но это тоже не совсем правильно, потому что он все равно пропускает непустой дубликат (если это вообще имеет смысл).
решение1
Примечание: ваш вопрос относится к9диапазон ячеек, но вы показываете10значения в примерах, так что это не совсем сходится... хотя я понимаю проблему.
Первая часть вашей формулы в порядке, потому что это стандартный способ подсчета количества различных непустых значений... но когда вы вычитаете вторую часть, COUNTIFвам также нужно исключить пробелы, поэтому вам нужно удалить часть &"" во второй части COUNTIF, т. е. эта версия
=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&"")-(COUNTIF(A2:A10,A2:A10)=1))
....но эта версия лучше
=SUMPRODUCT((COUNTIF(A2:A10,A2:A10)>1)/COUNTIF(A2:A10,A2:A10&""))
Обе эти формулы будут работать как для текстовых, так и для числовых данных в диапазоне A2:A10 (или для смеси того и другого), но только для числовых значений (как в вашем примере) вы также можете использовать FREQUENCYтакую функцию:
=SUMPRODUCT((FREQUENCY(A2:A10,A2:A10)>1)+0)