Показатели с несколькими диапазонами критериев ИЛИ

Question

Я использую формулу:

=SUMPRODUCT(
COUNTIFS(
'Data'!$I:$I,UK!$I$3,
'Data'!$O:$O,MONTH(UK!K$5),
'Data'!$P:$P,YEAR(UK!K$5),
'Data'!$D:$D,$A13,
'Data'!$E:$E,**$C13:$F13**
)
)

Моя проблема в том, что если я попытаюсь добавить еще один критерий диапазона, например,

'Data'!$A:A,**$C14:$F14** 

формула в итоге дает результат 0 вместо числа. Это не правильный результат. Есть ли способ иметь несколько диапазонов «ИЛИ» в одной формуле?

Answer 1

Чтобы сделать несколько операторов OR , один должен быть вертикальным, а другой горизонтальным.

Так что используйте транспонирование на второй:

=SUMPRODUCT(
COUNTIFS(
'Data'!$I:$I,UK!$I$3,
'Data'!$O:$O,MONTH(UK!K$5),
'Data'!$P:$P,YEAR(UK!K$5),
'Data'!$D:$D,$A13,
'Data'!$E:$E,$C13:$F13,
'Data'!$A:$A,Transpose($C13:$F13)
)
)

Ограничение составляет два таких массива в критериях, один горизонтальный и один вертикальный. 3 или более нельзя сделать без разбиения формул и их суммирования.

Также попробуйте использовать SUM() вместо SUMPRODUCT. В теории это должно работать с СУММ как регулярная формула.

Answer 2

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

1. Независимы ли диапазоны? Например, элементы в столбце I не влияют ни на один из других столбцов, поэтому задайте имя только для этого диапазона.

Пример формулы [1] = countifs(Данные '!$ I:$ I, Великобритания!$ I $ 3) назовите это UK_I (используйте менеджер имен на ленте формул. Создайте новое имя). Продолжите этот же формат, где создайте имя для каждого независимого диапазона данных.

2. Должны ли выполняться два или более условия, чтобы это считалось? Нужно, чтобы месяц и год были правильными. Сделать счетчик, который имеет 2 диапазона.

Пример формулы [2] = Countifs('Данные'!$ O:$ O, МЕСЯЦ (UK!K $ 5), «Данные»!$ P:P $, YEAR (UK!K $ 5)) назови это UK_K

3. После того, как все элементы, сумма (UK_I, UK_K и т.д. .....) Позволяет суммировать их.

Это легко настроить, если оно работает, изменив соответствующее имя или добавив новое имя для добавления дополнительных данных.