Общая формула
SUMPRODUCT(--(A:A=A1))
Резюме
Предисловие
Это досадно длинное введение, но контекст важен, извините!
Если вы попытаетесь подсчитать очень длинные числа (16+ цифр) в диапазоне с помощью COUNTIF, вы можете увидеть неверные результаты из-за ошибки в том, как определенные функции обрабатывают длинные числа, даже если эти числа хранятся в виде текста. Рассмотрим экран ниже. Все значения в столбце D неверны - хотя каждое число в столбце B уникально, счет, возвращаемый функцией COUNTIF, предполагает, что эти числа дублируются.
=COUNTIF(data,B5)
Эта проблема связана с тем, как Excel обрабатывает числа. Excel может обрабатывать только 15 значащих цифр, и если вы введете число, состоящее из более чем 15 цифр в Excel, вы увидите, что конечные цифры автоматически преобразуются в ноль. Упомянутая выше проблема подсчета возникает из этого предела.
Обычно этого ограничения можно избежать, вводя длинные числа в виде текста, либо начав число с одинарной кавычки ('999999999999999999), либо отформатировав ячейку (я) как текст перед вводом. Если вам не нужно выполнять математические операции с числами, это хорошее решение, и оно позволяет вводить очень длинные числа для таких вещей, как номера кредитных карт и серийные номера, без потери номеров.
Однако, если вы попытаетесь использовать СЧЁТЕСЛИ для подсчета числа, состоящего более чем из 15 цифр (даже при сохранении в виде текста), вы можете увидеть ненадежные результаты. Это происходит потому, что COUNTIF внутренне преобразует длинное значение обратно в число в какой-то момент во время обработки, вызывая ограничение в 15 цифр, описанное выше. Без всех цифр некоторые числа могут считаться дубликатами при подсчете с помощью СЧЁТЕСЛИ.
Решение
Одно из решений - заменить формулу СЧЁТЕСЛИ формулой, в которой используется СУММ или СУММПРОИЗВ. В показанном примере формула в E5 выглядит так:
=SUMPRODUCT(--(data=B5))
Формула использует именованный диапазон «данные» (B5: B9) и генерирует правильное количество для каждого числа с помощью SUMPRODUCT.
Объяснение
Сначала выражение внутри SUMPRODUCT сравнивает все значения в названном диапазоне «данные» со значением из столбца B в текущей строке. Это приводит к массиву результатов ИСТИНА / ЛОЖЬ.
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
Затем двойное отрицание переводит значения ИСТИНА / ЛОЖЬ в значения 1/0.
=SUMPRODUCT((1;0;0;0;0))
Наконец, СУММПРОИЗВ просто суммирует элементы в массиве и возвращает результат.
Вариант формулы массива
Вы также можете использовать функцию СУММ вместо СУММПРОИЗВ, но это формула массива, и ее необходимо вводить с помощью Ctrl + Shift + Enter:
(=SUM(--(B:B=B5)))
Другие функции с этой проблемой
Я сам не проверял это, но похоже, что несколько функций имеют одну и ту же проблему, включая СУММЕСЛИ, СУММЕСЛИ, СЧЁТЕСЛИ, СЧЁТЕСЛИ, СРЕДНЕЛИ и СРЕДНЕЛИ.
