Here's the excel table:

enter image description here

In B16 I'd like to insert the sum of all the cells in the range B1:B10 that have "*" on the right. In this case B16 = 10 + 2 + 7 + 9 = 28

How can I do this with a formula?

P.S. I'm using Excel 2010.


You can use SUMIF function:

SUMIF( range, criteria, [sum_range] )

So in your case you would place in b16:

=SUMIF(c1:c10, "~*", b1:b10)

The tilde (~) in front of the * is to prevent the * of being used as a wildcard that could match anything non blank. The * alone only worked because there where only stars or blank cells in the criteria column but any other character would match. Credits to barry houdini in his comments.

  • yep, that's the one you need. @Sergey – Jacob Jan Tuinstra Dec 12 '12 at 11:58
  • 1
    Be careful with * as the criteria because that's a "wildcard" character in SUMIF. If C1:C10 is only blank or * then no problem but, in fact, any non-numeric value in C1 would cause B1 to be added to the total, e.g. "xyz" or "A". To sum for * only use a tilde in front of the * to indicate that it's not a wildcard, i.e. =SUMIF(c1:c10, "~*", b1:b10) – barry houdini Dec 12 '12 at 19:16
  • I didn't knew the * being a wildcard but I tested in LibreOffice and * between quotes is not a wildcard there (only the line with * is summed. No Office 2010 to test here... :) – laurent Dec 13 '12 at 0:41
  • 1
    OK - I don't know LibreOffice, Excel 2010 Help for SUMIF says "You can use the wildcard characters — the question mark (?) and asterisk (*) — as the criteria argument. 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" – barry houdini Dec 13 '12 at 21:16

In column D, put


Then copy this down, and sum row D.

  • 2
    There is no need for an additional column (see laurent's answer with SUMIF) – Matteo Dec 12 '12 at 11:44
  • 1
    @Matteo Well I would have suggested sumif if I had been aware of the global column shortage – Paul Dec 12 '12 at 12:36
  • 2
    It's just cosmetic. I usually avoid to put additional columns for computations (if I can) to avoid confusion to other people using/viewing the sheet. Additionally adding rows could lead to empty cells in the additional column which could go unnoticed... – Matteo Dec 12 '12 at 13:34

If you change the * into a 1 (style it with some color), then you can use SUMPRODUCT:


In google spreadsheets it would be like this:


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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