Sumifs / left formulation

F

Francis Gerard Tupaz

Member
Joined
Jun 4, 2012
Messages
31
Reaction score
0
Points
6
Location
Philippines
Excel Version(s)
2011
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
 

Attachments

  • Sample Data.xlsx
    12.6 KB · Views: 294
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)
 
Bob Phillips said:
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)
Click to expand...

Hi Sir Bob,

Sorry I attached awrong attachement and when I tried the formula you nestled it does not work, can you help me please again? I have attached the right sample data. thank you.

Regards,
 

Attachments

  • Sample Data 1.xlsx
    13.8 KB · Views: 161
Hi Sir Bob,

Sorry I attached awrong attachement and when I tried the formula you nestled it does not work, can you help me please again? I have attached the right sample data. thank you.

Regards,


Francis
Bob Phillips said:
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)
Click to expand...
 

Attachments

  • Sample Data 1.xlsx
    14.1 KB · Views: 225
Try

=SUMPRODUCT(--(LEFT($B$5:$B$29,FIND( " -",$B$5:$B$29)-1)=$F7),--($D$5:$D$29=G$6),$A$5:$A$29)
 
Bob Phillips said:
Try

=SUMPRODUCT(--(LEFT($B$5:$B$29,FIND( " -",$B$5:$B$29)-1)=$F7),--($D$5:$D$29=G$6),$A$5:$A$29)
Click to expand...

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)))
 
You must log in or register to reply here.
Back
Top