Wildcard in SUM

[Guest]

I'm hoping someone will add to be able to show me what I'm doing wrong. I'll
try to show a simple example.
Spreadsheet view:
A B C
1 11COH1092069/15 CPO 14
2 11COH1092069/15 14
3 11COH1092069/15 CPO 14
4 11COH1092069/14 14

Formula that I would like to use:
=(SUM(B1:B4="CPO")*(A1:A4="*2069/15*")*C1:C4)
It doesn't work, any ideas? Thx - Dean

 

[Guest]

Give SumProduct a try...

=SUMPRODUCT(--(ISNUMBER(FIND("2069/15", $A$1:$A$4))), --($B$1:$B$4 =
"CPO"),$C$1:$C$4)

Take a look at this for an explanation (Example 9)...
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[theSquirrel]

I'm hoping someone will add to be able to show me what I'm doing wrong. I'll
try to show a simple example.
Spreadsheet view:
A B C
1 11COH1092069/15 CPO 14
2 11COH1092069/15 14
3 11COH1092069/15 CPO 14
4 11COH1092069/14 14

Formula that I would like to use:
=(SUM(B1:B4="CPO")*(A1:A4="*2069/15*")*C1:C4)
It doesn't work, any ideas? Thx - Dean

I don't think I understand what you are trying to do with the
'SUM(B1:B4="CPO")' statement. It looks like you want to count the
number of times that "CPO" shows up in the range. If this is the
case, you should use 'Countif(B1:B4,"CPO")' instead.

As for the second expression '(A1:A4="*2069/15*")' it looks like you
want to use the same type of thing, but I am unsure about the
wildcards being correct. I think you will need to use some sort of
search or parse to see if the value has that particular string and
then count it. A bit more complex but not impossible.

 

[Guest]

Dean,

here's one way.

=SUM((B1:B4="CPO")*(IF(ISERROR(FIND("2069/15", A1:A4)), FALSE, TRUE))*C1:C4)

 

[Toyin.Butler]

Dean,

Assuming you are trying to sum the corresponding cells that end in
2069/15 in the first column and also have CPO in the second column you
need to do the following:

=RIGHT(TRIM(A1),7)&B1 in column (copy down)

the formula to sum the relevant cells is then

=SUMIF(D14,"2069/15CPO",C1:C4)

HTH

Toyin.

 

[Guest]

Vergel,
Your formula works terrific as an array. I have a question, I've
read in some of the other questions in this site, that SUMPRODUCT has some
limitations on how many rows of data that it will handle accurately and that
SUM doesn't have that limitation, in your estimation is that true? Possilby
Jim(1st responder) has an opinion on this as well? Thanks for your response,
I really appreciate it. Dean

 

[Guest]

I have received a couple of really great responses that work. I was
trying to get the quantity total, based on the conditions mentioned of
searching for CPO in Column B, then searching for a portion of the string
2069/15 in Column A. But anyway, looks like I'm good now. Thanks for your
response. Dean

 

[Guest]

Toyin,
Thanks for your response, I've got what I need now, but your solution
has some good steps for some other applications. Thanks again. Dean

 

[Guest]

Jim,
I tried that very formula earlier after having gone to the site that
you mentioned, but it never dawned on me that I didn't need to show the
asterisk as a wildcard. It works absolutely great. Thanks for your
response. Dean