Results 1 to 7 of 7

Thread: Sumifs / left formulation

  1. #1

    Sumifs / left formulation



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #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)

  3. #3

    Sumifs / left formulation

    Quote Originally Posted by Bob Phillips View Post
    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,
    Attached Files Attached Files

  4. #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
    Quote Originally Posted by Bob Phillips View Post
    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)
    Attached Files Attached Files

  5. #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)

  6. #6

    Sumifs / left formulation

    Quote Originally Posted by Bob Phillips View Post
    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

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

Posting Permissions

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