# How can I make a SUM of cells that match a specific criteria?

Here's the excel table:

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 Dec 12, 2012 at 11:58
• 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) Dec 12, 2012 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... :) Dec 13, 2012 at 0:41
• 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" Dec 13, 2012 at 21:16

In column D, put

``````=if(c1=="*",b1,0)
``````

Then copy this down, and sum row D.

• There is no need for an additional column (see laurent's answer with SUMIF) Dec 12, 2012 at 11:44
• @Matteo Well I would have suggested sumif if I had been aware of the global column shortage
– Paul
Dec 12, 2012 at 12:36
• 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... Dec 12, 2012 at 13:34

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

``````=SUMPRODUCT(B1:B10,C1:10)
``````

``````=SUM(FILTER(B1:B10,C1:C10="*"))