2

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.

Improve this question

3 Answers 3

Reset to default
9

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.

Improve this answer
4
  • yep, that's the one you need. @Sergey
    – Jacob Jan Tuinstra
    Dec 12, 2012 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, 2012 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, 2012 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, 2012 at 21:16
2

In column D, put

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

Then copy this down, and sum row D.

Improve this answer
3
  • 2
    There is no need for an additional column (see laurent's answer with SUMIF)
    – Matteo
    Dec 12, 2012 at 11:44
  • 1
    @Matteo Well I would have suggested sumif if I had been aware of the global column shortage
    – Paul
    Dec 12, 2012 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, 2012 at 13:34
2

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="*"))
Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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