Excel: как суммировать значения в столбце на основе нескольких критериев

Question

У меня есть список критериев в строке 20, например:

A20: "My Company, Inc."
B20: "Client Expenses"
C20: "Travel"

Данные ниже, вот так:

B40: "Client Expenses"
C40: "My Company, Inc."
D40: "Travel"
I40: $100

B41: "Client Expenses"
C41: "Acme, Inc."
D41: "Travel"
I41: $200

B42: "Client Expenses"
C42: "My Company, Inc."
D42: "Food"
I42: $300

B43: "Client Expenses"
C43: "My Company, Inc."
D43: "Travel"
I43: $400

Я хочу суммировать весь столбец I, если критерии в A, B, C: 20 соответствуют данным в B, C, D: 40:43

В OpenOffice Calc это работает:

=SUMPRODUCT($C$40:$C$66=A27;$D$40:$D$66=C27;$B$40:$B$66=B27;$I$40:$I$66)

В Excel для Mac OS X это дает мне 0 долларов. Может кто-нибудь помочь мне или исправить это, или придумает другой метод, чтобы сделать то же самое?

Answer 1

Оберните каждый аргумент в скобках и добавьте две черты, предшествующие им. Вот это переписано:

=SUMPRODUCT(--($C$40:$C$66=A27),--($D$40:$D$66=C27),--($B$40:$B$66=B27),$I$40:$I$66)

Это должно работать.

Answer 2

В зависимости от настроек местного языка вам может потребоваться заменить точку с запятой на запятую.

Если вы используете запятые в качестве десятичного разделителя, например 123,45 (как в большинстве стран Европы), то для разделения аргументов в формулах обычно используются точки с запятой.

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

=SUMPRODUCT($C$40:$C$66=A27,$D$40:$D$66=C27,$B$40:$B$66=B27,$I$40:$I$66)

В качестве альтернативы, попробуйте использовать SumifS (более новую функцию для выполнения SUMIF с несколькими критериями) или, возможно, DSUM (что позволит вам добавить условия "ИЛИ", а также "И", например:

Company = "Acme" AND (Тип расходов = "Travel" ИЛИ "Food")

Answer 3

Помогло ли это вам заменить точку с запятой на запятую?

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