Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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


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
up vote 7 down vote accepted

Not particularly elegant, but:


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

share|improve this answer

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


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? 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. – Mar 3 '12 at 4:57

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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .