Sumifs / left formulation

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

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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)
 
Joined
Jun 4, 2012
Messages
31
Reaction score
0
Points
6
Location
Philippines
Excel Version(s)
2011
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,
 

Attachments

  • Sample Data 1.xlsx
    13.8 KB · Views: 161
Joined
Jun 4, 2012
Messages
31
Reaction score
0
Points
6
Location
Philippines
Excel Version(s)
2011
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)
 

Attachments

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

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Try

=SUMPRODUCT(--(LEFT($B$5:$B$29,FIND( " -",$B$5:$B$29)-1)=$F7),--($D$5:$D$29=G$6),$A$5:$A$29)
 
Joined
Jun 4, 2012
Messages
31
Reaction score
0
Points
6
Location
Philippines
Excel Version(s)
2011
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

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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)))
 
Top