Wildcard for Conditional Sum Wizard statement.

R

ron

In a simple SUMIF function this
=SUMIF(A2:A5,"CAD-EC-*",B2:B5)
works fine for summing the two CAD-EC entries below ($9,000).

A B
CAD-EC-001 $4,000
CAD-GC-001 $1,000
CAD-PC-001 $1,000
CAD-EC-002 $5,000

But when I use the Conditional Sum Wizard,
=SUM(IF(A2:A5="CAD-EC-*",B2:B5,0))
This “CAD-EC-*†doesn’t work. Or does it, and I don’t know how to use
it. The reason I need to use the conditional sum wizard is that I actually
have other conditions, I'm just trying to keep it simple to find the correct
wildcard usage.
 
V

vezerid

No, you are right, it does not work this way, because you are
attempting equality. Try this:

=SUM(IF(ISNUMBER(SEARCH("CAD-EC-",A2:A5)),B2:B5,0))

This is an *array* formula, thus you need to commit it with Ctrl+Shift
+Enter. A non-array version is with SUMPRODUCT:

=SUMPRODUCT(B2:B5*ISNUMBER(SEARCH("CAD-EC-",A2:A5)))

HTH
Kostis Vezerides
 
J

JE McGimpsey

Wildcards only work with SUMIF and COUNTIF, not IF().

You can use other techniques, for instance:

=SUMPRODUCT(--(LEFT(A2:A5,7)="CAD-EC-"))

which you can generalize to add conditions.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top