Используя условие ИЛИ в SUMPRODUCT

Question

У меня есть три столбца данных - столбец суммы (A) и два столбца, которые образуют сумму (B и C). Иногда заполняется только один из столбцов B и C ; иногда столбцы B и C пустые.

Я хочу суммировать столбец A (т. Е. Итоговую сумму), только когда есть данные в столбцах B и / или C Итак, я надеялся, что будет работать следующее:

=SUMPRODUCT(--((B2:B10<>"")+(C2:C20<>"")),A2:A10)

Но проблема в том, что если в обоих столбцах B и C есть данные, то массив, образованный из (B2:B10<>"")+(C2:C10<>"") составит 2, а предыдущий -- не t преобразовать его в TRUE (например, {1,1,0,1,0,1,1,1,0} + {1,0,1,1,0,1,1,0,1} = {2,1,1,2,0,2,2,1,1}), и этот окончательный массив затем умножается на значения в столбце A , таким образом удваивая значения 1, 4, 6 и 7.

Это кажется странным поведением! Как мне заставить его вести себя правильно ?!

Answer 1

Вы могли бы сделать что-то вроде этого:

=SUMPRODUCT($A$2:$A$11*($B$2:$B$11&$C$2:$C$11<>""))

Идея состоит в том, что объединение B и C является пустым, только если оба являются пустыми.

Answer 2

Ага - мне нужна была функция SIGN :

=SUMPRODUCT(A2:A10,SIGN((B2:B10<>"")+(C2:C10<>"")))

Я все еще нахожу странным, что, что касается SUMPRODUCT, TRUE + TRUE = 2. И это 2 не просто оценивается как ИСТИНА, когда -- в игре.

Answer 3

Я думаю, что вы можете рассмотреть функцию =SUMIFS() вместо.

Ваше состояние может быть переписано как:

• Условие 1: столбец B заполнен, а столбец C пуст; ИЛИ ЖЕ
• Условие 2: столбец B не заполнен, а столбец C заполнен; ИЛИ ЖЕ
• Условие 3: столбец B и столбец C заполнены.

Поскольку эти 3 условия являются взаимоисключающими, сложный SUMPRODUCT по существу =SUMIF(Condition 1)+SUMIF(Condition 2)+SUMIF(Condition 3) . Переписано как SUMIFS:

=SUMIFS(A2:A10,B2:B10,"<>",C2:C10,"")+SUMIFS(A2:A10,B2:B10,"",C2:C10,"<>")+SUMIFS(A2:A10,B2:B10,"<>",C2:C10,"<>")

С точки зрения производительности это решение может быть лучше, чем использование SUMPRODUCT а также при наличии большого количества строк для суммирования.