SUM condition on one set of columns where another includes certain values

Question

I have the following data in Excel where I want a formula that will give me the sum of all of the total rows:

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

I have tried formulae along the lines of

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

but I cannot get the condition to work.

What is the correct way to write this contains condition?

Is there also specific a grammar for the condition language? I've been unable to find anything.

Answer 1

Not particularly elegant, but:

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

For some reason you can't use A:A or B:B in it...

Answer 2

I found a way to write this using sumif and a wildcard in the condition

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

However I prefer the use of sumproduct as this allows me to write normal Excel formulas instead of guessing at the condition syntax.

Answer 3

Are the total rows actually calculated as totals, or are they just flat data imported from somewhere along with the breakdown values?

If they are calculated, then a simple trick would be this: Don't sum the totals using SUM, use SUBTOTAL (using option 9 for the sum type of subtotal). so eg in B2 you would have =sSUBTOTAL(9,B1:B1), in B6 =SUBTOTAL(9,B3:B5) and so on.

Now you can simply use another SUBTOTAL function to add up the whole of column B, and it will ignore the other subtotals completely. This is in effect the reverse of adding up the totals, it adds up only the original data points, but in some ways that is more robust if you had an incorrect total line due to an incorrect formula. This technique works in all versions of Excel which have the subtotal function and is discussed briefly here because of a related change in the status bar in Excel 2010: A couple of quick Excel 2010 discoveries

Answer 4

Strictly speaking, this is not an answer to the question that was asked, but it does solve the problem represented by the example.  Given that B2=B1 and B6=B3+B4+B5 in the example, we can notice that B1+B2+B3+B4+B5+B6 = (B1)+B2+(B3+B4+B5)+B6 = B2+B2+B6+B6 = 2×(B2+B6).  Since B2+B6 is the value we are trying to obtain (in this limited example), it follows that we can get it by SUM(B:B)/2.