Hello all,
I am trying to sum based on wildcard criteria (please see attached).
I tried few attempts yet neither worked.
Could you please advise?
Thank you and best regards.
Capture.PNG
Hello all,
I am trying to sum based on wildcard criteria (please see attached).
I tried few attempts yet neither worked.
Could you please advise?
Thank you and best regards.
Capture.PNG
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)
Last edited by Pepe Le Mokko; 01-26-2019 at 10:45 AM. Reason: removed wildcards
Please try
=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)
Hi,
Thank you very much and sorry I couldn't upload the workbook (think is a bug).
Your solution worked perfectly.
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,
Just be aware that FIND is case sensitive
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 .
Last edited by Bo_Ry; 01-26-2019 at 11:00 AM. Reason: Typo
Thank you all for prompt solution and explanation.
Best regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks