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

Resources