Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
add comment

4 Answers

up vote 7 down vote accepted

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...

share|improve this answer
add comment

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.

share|improve this answer
    
What is there to guess? office.microsoft.com/en-us/excel-help/sumif-HP005209292.aspx You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character. –  ta.speot.is Mar 3 '12 at 4:57
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.