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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 29, 2024

ReginaAnn 

Perhaps you mean something like

=SUM(
  SUMPRODUCT(
  (HX!DV2:DV63590 = 18)*
  (HX!Q2:Q63590 = "0735")*
  (
     (--HX!P2:P63590 = 94001) +
     (--HX!P2:P63590 = 91142) +
     (--HX!P2:P63590 = 94004)
  ) *
  (
     (HX!EN2:EN63590 = "5500") +
     (HX!EO2:EO63590 = "5500") +
     (HX!EP2:EP63590 = "5500") +
     (HX!EQ2:EQ63590 = "5500")
  ) *
  --HX!BC2:BC63590 )
)

In any case you need to start from the second row and check where are numbers and where are texts which looks like numbers.

Your sample file has no HX sheet and I'm not sure columns in Sheet1 are mapped on ones used in formula. Thus above is just my guess.

Resources