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


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

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.

1 Answer

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.

In column D, put

=if(c1==”*”,b1,0)

Then copy this down, and sum row D.

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

=SUMPRODUCT(B1:B10,C1:10)

In google spreadsheets it would be like this:

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

Leave a Reply

Your email address will not be published. Required fields are marked *