Error in Sumproduct formula

[sachar]

Dear All,

With reference to the attach sample file, May I know what is the wrong with Sumproduct formula the worksheet Cell ”D5” that should be the total.

Please help me?

 

[Khalid NGO]

Hi,

SUMPRODUCT doesn't work with wildcards.

Try SUMIFS, if you are looking for AND condition:
=SUMIFS(C5:C84,B5:B84,"*Zighy Bar*",B5:B84,"*BF*")

Or this if you need OR condition:
=SUM(SUMIF(B5:B84,"*"&{"Zighy Bar","BF"}&"*",C5:C84))

Regards,

 

[sachar]

Dear,

Received with thanks, I applied the both formulas, the number one formula is getting the correct answer as cell “E7” but, the other one is wrong answer in Cell “E5”.(file attached)

Can you please justify,

Thanking you.

 

[pecoflyer]

Some explanation on what you are trying to do and expetced answer might help

 

[Khalid NGO]

Can you please justify,

Hi,
Dear I have already justified in my first post

Okay I clarify more.

1st formula:
=SUMIFS(C5:C84,B5:B84,"*Zighy Bar*",B5:B84,"*BF*")
is searching both strings i.e. Zighy Bar AND BF in column B, so it will sum only when the both blue highlighted strings found in the same cell.

2nd formula:
=SUM(SUMIF(B5:B84,"*"&{"Zighy Bar","BF"}&"*",C5:C84))
I was not sure about your expected answers therefore I assumed you may look for the sum if any of the strings either Zighy Bar OR BF is found.

Hope that helps.