Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 9 down vote accepted

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.

share|improve this answer
    
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
    
OK corrected answer - thanks for the help – laurent Dec 14 '12 at 0:56

In column D, put

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

Then copy this down, and sum row D.

share|improve this answer
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:

=SUMPRODUCT(B1:B10,C1:10)

In google spreadsheets it would be like this:

=SUM(FILTER(B1:B10,C1:C10="*"))
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .