Forum Discussion

ReginaAnn's avatar
ReginaAnn
Copper Contributor
Jul 29, 2024
Solved

SUM PRODUCT - Multiple Critera Failing at Last Arguement

Good Morning,   I am having trouble with the following formula.  It seems to bee faling at the * HX!BC1:BC63590 criteria.  I could really use help with this.  I am also attaching a small data set. ...
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Jul 31, 2024

    ReginaAnn 

    This formula

    =SUMPRODUCT(
 (HX!DV2:DV66410 = "-") *
 (--HX!Q2:Q66410 = 735) *
 (
   (
    (--HX!P2:P66410 = 90327) +
    (--HX!P2:P66410 = 91142) +
    (--HX!P2:P66410 = 99327)
    > 0
   )
 ) *
 (
    (
       (--HX!DL2:DL66410 = 5500) +
       (--HX!DL2:DL66410 = 5500) +
       (--HX!DL2:DL66410 = 5500) +
       (--HX!DL2:DL66410 = 5500)
       > 0
    )
 ) *
 HX!BC2:BC66410 )

    returns some result, but here are not exactly the same column as in sample. In general

    - you need to define which columns to use. If they could be in different positions it's better select columns based on headers in first row

    - Do you have numbers, or texts which looks like numbers, or any other text like "-", or combination of texts and numbers in columns

     

    Depends on above formula could be modified to make it more universal.

ReginaAnn's avatar
ReginaAnn
Copper Contributor
Jul 29, 2024

JoeUser2004 

Thank you once again for your prompt assistance.

I have updated the sample data and made the necessary changes to each of the formulas (please see the attached file). Despite these adjustments, I am still encountering zeros with both your formula and Sergei's formula. I am unsure why this is occurring.

 

Interestingly, my own formula produces the correct value. However, I am concerned that my formula might be causing a double count. If that’s the case, could you please advise on the best way to correct this issue?

 

Your insights and suggestions would be greatly appreciated.

 

Thank you for your continued support.

 

Best regards,
Regina

 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 30, 2024

ReginaAnn 

With the latest sample initial formula could be

=SUMPRODUCT(
  (HX!AB2:AB63590 = 18)*
  (HX!Q2:Q63590 = "0735")*
  (
     (HX!P2:P63590 = "90327") +
     (HX!P2:P63590 = "91142") +
     (HX!P2:P63590 = "99327")
  ) *
  (  (
     (HX!AC2:AC63590 = "5500") +
     (HX!AD2:AD63590 = "5500") +
     (HX!AE2:AE63590 = "5500") +
     (HX!AF2:AF63590 = "5500")
     ) > 0
  ) *
  HX!Y2:Y63590 )
  • ReginaAnn's avatar
    ReginaAnn
    Copper Contributor
    Jul 30, 2024

    SergeiBaklan 

     

    Thank you for this new code.  It does work in the example; however, it does not work in my larger data set.  I've updated the formula as below to correspond to the attached file.  Not sure why it's not working.  Any help you can provide would be greatly appreciated.

     

    =SUMPRODUCT( (HX!DV2:DV66410 = 22)* (HX!Q2:Q66410= "0735")* ((HX!P2:P66410= "90327") + (HX!P2:P66410= "91142") + (HX!P2:P66410= "99327") ) * ( ( (HX!EN2:EN66410 = "5500") + (HX!EO2:EO66410 = "5500") + (HX!EP2:EP66410 = "5500") + (HX!EQ2:EQ66410 = "5500") ) > 0 ) * HX!BC2:BC66410)

     

    Thank you,

     

    Regina

     

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jul 31, 2024

      ReginaAnn 

      This formula

      =SUMPRODUCT(
 (HX!DV2:DV66410 = "-") *
 (--HX!Q2:Q66410 = 735) *
 (
   (
    (--HX!P2:P66410 = 90327) +
    (--HX!P2:P66410 = 91142) +
    (--HX!P2:P66410 = 99327)
    > 0
   )
 ) *
 (
    (
       (--HX!DL2:DL66410 = 5500) +
       (--HX!DL2:DL66410 = 5500) +
       (--HX!DL2:DL66410 = 5500) +
       (--HX!DL2:DL66410 = 5500)
       > 0
    )
 ) *
 HX!BC2:BC66410 )

      returns some result, but here are not exactly the same column as in sample. In general

      - you need to define which columns to use. If they could be in different positions it's better select columns based on headers in first row

      - Do you have numbers, or texts which looks like numbers, or any other text like "-", or combination of texts and numbers in columns

       

      Depends on above formula could be modified to make it more universal.

      • ReginaAnn's avatar
        ReginaAnn
        Copper Contributor
        Jul 31, 2024

        SergeiBaklanthanks for being tenacious.  I was able to get this to work with your help. 

         

        Have a wonderful rest of the day!

         

        Regina

Resources