Sumproduct

[sachar]

Dear,

With reference to the attached sample file, May I know what is the wrong with Sumproduct Formula in Colum “H5” that is returning ”0.00” value ”!

Thanking you.

 

[Faseeh]

Would you like to use SUMIF instead.

=SUMIF(E5:E70,"*"&"Room"&"*",F5:F70)

 

[Hui]

=SUMPRODUCT((LEN(E5:E70)-LEN(SUBSTITUTE(E5:E70,"Room",""))<>0)*($F$5:$F$70)) Ctrl+Shift+Enter

This uses part of the solution discussed here:
http://chandoo.org/wp/2011/11/07/formula-forensics-002/

 

[Asheesh]

Hi,

Try the below

SUMPRODUCT(ISNUMBER(SEARCH("Room",$E$5:$E$70))*F5:F70)

 

[Hui]

and remarkably they all return the same answer $20,124.88

 

[Asheesh]

 

[Khalid NGO]

On more CSE:
=SUM(IFERROR(FIND("Room",$E$5:$E$70),0)*($F$5:$F$70))

 

[Deepak]

Is this..

=SUMIF($E$5:$E$70,"*"&"Room"&"*",$F$5:$F$70)