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

 

JoeUser2004's avatar
JoeUser2004
Bronze Contributor
Jul 29, 2024

ReginaAnn  wrote: "my own formula produces the correct value"

 

Okay.  I might have misunderstood the logic, especially since SUMIFS with an array constant in the condition behaves differently in my version of Excel.

 

-----

ReginaAnn  wrote: "I am concerned that my formula might be causing a double count"

 

Yes.  But I am not interested in trying to make the SUM(SUMIFS(...), SUMIFS(...), ...) formula work -- if that is even possible.

 

However, I overlooked the fact that you had a similar problem in your original formula, as I corrected  it.

 

The condition ((HX!P2:P41 = 94001) + (HX!P2:P41 = 91142) + (HX!P2:P41 = 94004)) works as intended (OR) because the terms are mutually-exclusive:  for each row, Px can only be one value at a time.

 

However, that is not necessarily true for the condition ((HX!AC2:AC41 = "5500") + (HX!AD2:AD41 = "5500") + (HX!AE2:AE41 = "5500") + (HX!AF2:AF41 = "5500")), unless you want to rely on the assumption that the user (you?) will ensure that "5500" is in at most only one of those columns at a time.

 

If only for "defensive programming", it would be better to write

 

((HX!AC2:AC41 = "5500") + (HX!AD2:AD41 = "5500")
 + (HX!AE2:AE41 = "5500") + (HX!AF2:AF41 = "5500") > 0)

 

 

-----

ReginaAnn  wrote:  " I am still encountering zeros with both your formula and Sergei's formula"

 

The primary problem is:  the value in P31, P35 and P41 is not one of 94001, 91142 or 94004, as P32 is.

 

The second problem in my version is:  the values in P31, P32, P35 and P41 are text, not numeric.

 

(Looks can be deceiving, and the format of the cell does not matter.  Use formulas of the form =ISTEXT(P31) to confirm.)

 

Both problems can be fixed by replacing ((HX!P2:P41 = 94001) + (HX!P2:P41 = 91142) + (HX!P2:P41 = 94004)) with

 

(HX!P2:P41 = {"94001","91142","94004","90327","99327"})

 

 

With those changes, my SUMPRODUCT formula now returns the expected result in the sample data.

=SUMPRODUCT((HX!AB2:AB41 = 18) * (HX!Q2:Q41 = "0735")
 * (HX!P2:P41 = {"94001","91142","94004","90327","99327"})
 * ((HX!AC2:AC41 = "5500") + (HX!AD2:AD41 = "5500")
    + (HX!AE2:AE41 = "5500") + (HX!AF2:AF41 = "5500") > 0)
 * HX!Y2:Y41)

 

-----

 

Alternatively, in addition to adding tests for P2:P41=90327 and P2:P41=99327 (or changing the values in P31, P35 and P41), you could employ one of Sergei's fixes, namely --HX!P2:P41 instead of simply HX!P2:P41.  The double negation converts "numeric text" into bona fide numbers.

 

And arguably, that might be a good idea if only for "defensive programming".

 

To that end, I note that whereas 18 in column AB is numeric, all of the other values in column AB are text.  So, in general, the first condition should be (--HX!AB2:AB41 = 18).  Alternatively, change numeric 18 to text, and write (HX!AB2:AB41 = "18").

 

But note that "* --HX!Y2:Y41" at the end would be unnecessary.  Any arithmetic operation (e.g. multiplication) is sufficient to convert "numeric text".  So leave "* HX!Y2:Y41" as-is.

Resources