Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 2 down vote favorite

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

3 Answers

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
up vote 2 down vote

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
up vote 2 down vote

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.