Как рассчитать значения, которые имеют более 1 вхождения в столбце

Question

В ячейке 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<>"") где он считает практически непустые ячейки. Конечно, если есть две (или более) пустых ячейки, то она повторно увеличивается, но это тоже не совсем правильно, потому что она по-прежнему пропускает непустую дубликат (если это вообще имеет смысл).

Answer 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)