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.
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.
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="*"))