# Thread: Sumifs / left formulation

1. ## Sumifs / left formulation

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  Reply With Quote

2. 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)  Reply With Quote

3. ## Sumifs / left formulation Originally Posted by 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)
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,  Reply With Quote

4. ## Sumifs / left formulation

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 Originally Posted by 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)  Reply With Quote

5. Try

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

6. ## Sumifs / left formulation Originally Posted by 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)
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  Reply With Quote

7. 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)))  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•