Функции SUMIF, COUNTIF и COUNTBLANK возвращают "#VALUE!" Ошибка

Симптомы

Формула, содержащая функции SUMIF, SUMIFS, СЧЁТЕСЛИ, СЧЁТЕСЛИ ИЛИ COUNTBLANK , может возвращать ошибку "#VALUE!" в Microsoft Excel.

Примечание.

Это поведение также применяется к Dfunctions, таким как DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DPRODUCT, DSTDEV,DSTDEVP, DSUM, DVAR и DVARP. Функции OFFSET и INDIRECT также имеют такое поведение.

Причина

Это происходит, когда формула, содержащая функцию, ссылается на ячейки в закрытой книге и ячейки вычисляются.

Примечание.

Если открыть книгу, на которые ссылается ссылка, формула работает правильно.

Обходной путь

Чтобы обойти это поведение, используйте сочетание функций SUM и IF вместе в формуле массива.

Примеры

Примечание.

Необходимо ввести каждую формулу в виде формулы массива. Чтобы ввести формулу массива в Microsoft Excel для Windows, нажмите клавиши CTRL+SHIFT+ВВОД.

СУММЕСЛИ

Вместо того чтобы использовать формулу, аналогичную следующей:

=СУММЕСЛИ([Источник]Лист1!$A$1:$A$8,"a",[Источник]Лист1!$B$1:$B$8)

Используйте следующую формулу:

=СУММ(ЕСЛИ([Источник]Лист1!$A$1:$A$8="a",[Источник]Лист1!$B$1:$B$8,0))

COUNTIF

Вместо того чтобы использовать формулу, аналогичную следующей:

=СЧЁТЕСЛИ([Источник]Лист1!$A$1:$A$8,"a")

Используйте следующую формулу:

=SUM(IF([Source]Sheet1!$A$1:$A$8="a",1,0))

COUNTBLANK

Вместо того чтобы использовать формулу, аналогичную следующей:

=COUNTBLANK([Источник]Sheet1!$A$1:$A$8)

Используйте следующую формулу:

=SUM(IF([Source]Sheet1!$A$1:$A$8=",1,0))

При использовании формулы массива SUM(IF()) используйте логическое И или ИЛИ , чтобы заменить функцию SUMIFS или СЧЁТЕСЛИ.

Статус

Такое поведение является особенностью данного продукта.

Дополнительная информация

Функция СУММЕСЛИ использует следующий синтаксис:

=СУММЕСЛИ(диапазон, условие, sum_range).

Дополнительные сведения см. в статье Исправление ошибки #VALUE! .

Справочные материалы

Дополнительные сведения о мастере, который может помочь в создании этих функций, щелкните Справка Microsoft Excel в меню Справка , введите суммарные значения, соответствующие условиям, с помощью мастера условной суммы в помощнике Office или мастере ответов, а затем нажмите кнопку Поиск , чтобы просмотреть раздел.

Для получения дополнительных сведений о формулах массива щелкните Справка Microsoft Excel в меню Справка , введите сведения об использовании формул для вычисления значений на других листах и книгах в помощнике по Office или мастере ответов, а затем нажмите кнопку Поиск , чтобы просмотреть раздел.