СЧЁТЕСЛИ на нескольких листах не работает с СУММПРОИЗВ

Question

У меня есть два листа ниже.

(Приведенный выше пример таблицы сильно упрощен для этого вопроса)

Я пытаюсь написать формулу, которая суммирует общее количество появлений буквы A в 2017 году (ответ — 3).

Эта формула работает, если я складываю две функции СЧЕТЕСЛИ для каждого листа:

=COUNTIF(INDEX(Sheet2!A2:C4, 0, MATCH(2017, Sheet2!A2:C2, 0)), "A") + COUNTIF(INDEX(Sheet3!A2:C4, 0, MATCH(2017, Sheet3!A2:C2, 0)), "A")

Я попытался объединить их в одну формулу ниже. Однако я получаю ошибку #Value. «Листы» — это именованный диапазон, состоящий из {Лист2, Лист3}.

=SUMPRODUCT(COUNTIF(INDEX(INDIRECT("'"&Sheets&"'!"&"A2:C4"), 0, MATCH(2017, INDIRECT("'"&Sheets&"'!"&"A2:C2"), 0)), "A"))

Почему приведенная выше формула выдает ошибку, когда я пытаюсь использовать именованный диапазон?

Answer 1

Я успешно воспроизвел первую формулу между листами, и она работает, но ее SUMPRODUCTнужно немного подправить, и ее следует записать так, и вы получите правильный результат.3:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!A3:C4"),"A")-COUNTIF(INDIRECT("'"&Sheets&"'!A2:C2"),"2017"))

Примечание.

• Где Sheetsименованный диапазон состоит из имени листа, участвующего в используемой формуле.

• В приведенной выше формуле сначала находится количество ячеек, имеющих значение A, 5а затем вычитается количество ячеек со значением 2017, 2, после чего формула возвращает 3,,, (5 As - 2 2017s) = 3.

• Если вы используете +знак, обычно используемый с несколькими COUNTIF, то в вашем случае вы получите 7, а если СЧЕТЕСЛИ разделена ,формулой, то возвращается5

:Отредактировано:

Поскольку иногда приведенная выше формула не работает должным образом, я хотел бы предложить следующую формулу массива (CSE):

{=SUM(IF(ISNUMBER(Sheet1!$A$170:$C$170),IF(Sheet1!$A$170:$C$170=2017,IF(Sheet1!$A$171:$C$172="A",1))))+SUM(IF(ISNUMBER(Sheet2!$A$170:$C$170),IF(Sheet2!$A$170:$C$170=2017,IF(Sheet2!$A$171:$C$172="A",1))))}

• Закончи это с помощьюCtrl+Shift+Enter