SUMIFS does not support expressions like that Francis, but SUMPRODUCT does.
=SUMPRODUCT(--(LEFT($B$4:$B$36,FIND("-",$B$4:$B$36)-1)=$H4),--($D$4:$D$36=I$3),$C$4:$C$36)
Dear Friends,
May I please request for you help in formulating a formula that combined "LEFT and SUMIFS", its difficult to explain and so I just aatached the excel sheet to share my inquiry.
Thank you very much!
Regards,
Francis
SUMIFS does not support expressions like that Francis, but SUMPRODUCT does.
=SUMPRODUCT(--(LEFT($B$4:$B$36,FIND("-",$B$4:$B$36)-1)=$H4),--($D$4:$D$36=I$3),$C$4:$C$36)
Try
=SUMPRODUCT(--(LEFT($B$5:$B$29,FIND( " -",$B$5:$B$29)-1)=$F7),--($D$5:$D$29=G$6),$A$5:$A$29)
Hi Sir Bob,
The formula works great, an additional question if I may because whenever there is a blank column or row the formula doesn't work it returns to "value" error.
How can I possibly resolve the error? Doest count if formula works also?
Thanks very much!
My best regards,
Francis
You need an array formula for that
=SUM(IF(($B$5:$B$29<>""),IF((LEFT($B$5:$B$29,FIND(" -",$B$5:$B$29)-1)=$F7)*($D$5:$D$29=G$6),$A$5:$A$29)))
Bookmarks