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.
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this communityHere'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.
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="*"))