S sachar Member Nov 17, 2015 #1 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. Attachments trial balance_17.11.xlsx trial balance_17.11.xlsx 14.2 KB · Views: 12
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 Excel Ninja Nov 17, 2015 #2 Would you like to use SUMIF instead. =SUMIF(E5:E70,"*"&"Room"&"*",F5:F70)
Hui Excel Ninja Staff member Nov 17, 2015 #3 =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/
=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 Excel Ninja Nov 17, 2015 #4 Hi, Try the below SUMPRODUCT(ISNUMBER(SEARCH("Room",$E$5:$E$70))*F5:F70)
Hui Excel Ninja Staff member Nov 17, 2015 #5 and remarkably they all return the same answer $20,124.88 Last edited: Nov 17, 2015
Khalid NGO Excel Ninja Nov 17, 2015 #7 On more CSE: =SUM(IFERROR(FIND("Room",$E$5:$E$70),0)*($F$5:$F$70))