Sumifs / left formulation

[Francis Gerard Tupaz]

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

[Bob Phillips]

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)

[Francis Gerard Tupaz]

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)

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 Gerard Tupaz]

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

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]

Try

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

[Francis Gerard Tupaz]

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

[Bob Phillips]

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)))