# SUMIF with wildcard

1. ## SUMIF with wildcard

Hello all,

I am trying to sum based on wildcard criteria (please see attached).
I tried few attempts yet neither worked.

Thank you and best regards.

Capture.PNG

2. ## Re: SUMIF with wildcard

Hi and welcome
without the sheet to work on try something along he lines of =SUMPRODUCT(--(isnumber(search(e6,A3:A5))),B3:B5) (1st attempt 5)

3. ## Re: SUMIF with wildcard

=SUMPRODUCT(SUMIFS(\$B\$2:\$B\$8,\$A\$2:\$A\$8,IFERROR(--(F2&{"","*"}),"*"&F2&"*")))

or similar to Pepe's

=SUMPRODUCT(ISNUMBER(FIND(F2,A2:A9))*B2:B9)

4. ## Re: SUMIF with wildcard

Hi,
Thank you very much and sorry I couldn't upload the workbook (think is a bug).
Still I don't understand why the wildcard is not picking all values in SUMIF function.
It's either approximate match (Attempt 1) or exact match (Attempt 2).
Even when i tried to combine those 2 results with SUMIFS it gave null result (Aattempt 3).
Could you please tell where I was wrong?

Capture.PNG

Thank you and best regards,

5. ## Re: SUMIF with wildcard

Just be aware that FIND is case sensitive

6. ## Re: SUMIF with wildcard

Wildcard only applies for Text, 999 in B4 is number.
attempt 3 is for AND condition, A2:A8 is number 999 and text *999*, it will never be True for both.

Pepe, Thanks. Dice use number, so I use Find, 2 letters shorter .

7. ## Re: SUMIF with wildcard

Thank you all for prompt solution and explanation.
Best regards

There are currently 1 users browsing this thread. (0 members and 1 guests)