PDA

View Full Version : Solved: Sumif with concatenate and wildcard



NY2DR
04-01-2008, 10:51 AM
Hello all,

Need help with counting and suming

I understand this part:
=COUNTIF(List!$L:$L,"*"&$A2&"*")
This uses a CONCATENATE and wildcard. It works very well

I also undersatnd this part:
{=SUM(IF(List!E1:E1500=$B1,1))}
This will count full matching text

This even works well if I want to count 2 criterias:
{=SUM(IF((List!L1:L1500=A2)*(List!E1:E1500=B1),1))}

But can I do something like this; count a full text and the other with a CONCATENATE and wildcard?
{=SUM(IF((List!L1:L1500="*"&$A2&"*")*(List!E1:E1500=B1),1))}
This does not work at all!

How could I make this work?
Thanks

Bob Phillips
04-01-2008, 12:09 PM
=SUMPRODUCT(--(ISNUMBER(FIND(A2,List!L1:L1500))),--(List!E1:E1500=B1))

if you want it to be case-sensitive,

=SUMPRODUCT(--(ISNUMBER(SEARCH(A2,List!L1:L1500))),--(List!E1:E1500=B1))

if not

NY2DR
04-01-2008, 12:42 PM
As usual XID, you've helped me once again.

Thank you!