Условие СУММ для одного набора столбцов, где другой содержит определенные значения

Question

У меня есть следующие данные в Excel , где я хочу формулу , которая даст мне сумму всех полных строк:

A                             B
australia                    10
australia total              10
china                         1
china                         5
china                         7
china total                  13

Я попробовал формулы в соответствии с

=sumif(A:A,"search("total",A:A)>0",B:B)

но я не могу заставить условие работать.

Как правильно написать это содержит условие?

Есть ли особая грамматика для языка условий? Я не смог ничего найти.

Answer 1

Не особенно элегантно, но:

=SUMPRODUCT((RIGHT(A2:A7,5)="total")*1,B2:B7)

По какой-то причине вы не можете использовать A:A или B:B в нем ...

Answer 2

Я нашел способ написать это, используя sumif и подстановочный знак в условии

=SUMIF(A2:A7,"*total",B2:B7)

Однако я предпочитаю использовать sumproduct поскольку это позволяет мне писать обычные формулы Excel, а не гадать на синтаксисе условия.

Answer 3

Являются ли итоговые строки фактически рассчитанными как итоги, или это просто плоские данные, импортированные откуда-то вместе со значениями разбивки?

Если они рассчитаны, то простой трюк будет следующим: не суммируйте итоги, используя SUM, используйте SUBTOTAL (используя опцию 9 для типа суммы промежуточных итогов). так, например, в B2 вы должны иметь = sSUBTOTAL (9, B1: B1), в B6 = SUBTOTAL (9, B3: B5) и так далее.

Теперь вы можете просто использовать другую функцию SUBTOTAL, чтобы сложить весь столбец B, и он полностью проигнорирует другие промежуточные итоги. По сути, это противоположно суммированию итогов, оно суммирует только исходные точки данных, но в некоторых случаях это более надежно, если у вас была неправильная строка итога из-за неверной формулы. Этот метод работает во всех версиях Excel, которые имеют функцию промежуточных итогов, и кратко обсуждается здесь из-за связанного изменения в строке состояния в Excel 2010: пара быстрых открытий Excel 2010

Answer 4

Строго говоря, это не ответ на заданный вопрос, но он решает проблему, представленную примером.  Учитывая, что B2 = B1 и B6 = B3+B4+B5 в примере, мы можем заметить, что B1+B2+B3+B4+B5+B6 = (B1)+B2+(B3+B4+B5)+B6 = B2+B2+B6+B6 = 2 × (B2+B6).  Поскольку B2+B6 - это значение, которое мы пытаемся получить (в этом ограниченном примере), из этого следует, что мы можем получить его с помощью SUM(B:B)/2 .