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.  Thanks,
 
=SUM(SUMPRODUCT(HX!DV1:DV63590 = 18)*
(HX!Q1:Q63590 = "0735")*((HX!P1:P63590 = 94001) + (HX!P1:P63590 = 91142) + (HX!P1:P63590 = 94004))*((HX!EN1:EN63590 = "5500") + (HX!EO1:EO63590 = "5500") + (HX!EP1:EP63590 = "5500") + (HX!EQ1:EQ63590 = "5500")) *
HX!BC1:BC63590 ))
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.

13 Replies

Sort By
  • JoeUser2004's avatar
    JoeUser2004
    Bronze Contributor
    Jul 29, 2024

    ReginaAnn 

     

    As Excel shows us (in my version), you have an extra right-parenthesis at the end.  Also, the use of SUM and SUMPRODUCT is redundant.  Choose one of the following:

     

     

    =SUM((HX!DV1:DV63590 = 18) * (HX!Q1:Q63590 = "0735")
* ((HX!P1:P63590 = 94001) + (HX!P1:P63590 = 91142)
   + (HX!P1:P63590 = 94004))
* ((HX!EN1:EN63590 = "5500") + (HX!EO1:EO63590 = "5500")
   + (HX!EP1:EP63590 = "5500") + (HX!EQ1:EQ63590 = "5500"))
* HX!BC1:BC63590)

     

    or

     

    =SUMPRODUCT((HX!DV1:DV63590 = 18) * (HX!Q1:Q63590 = "0735")
* ((HX!P1:P63590 = 94001) + (HX!P1:P63590 = 91142)
   + (HX!P1:P63590 = 94004))
* ((HX!EN1:EN63590 = "5500") + (HX!EO1:EO63590 = "5500")
   + (HX!EP1:EP63590 = "5500") + (HX!EQ1:EQ63590 = "5500"))
* HX!BC1:BC63590)

     

     

    The SUM version works in versions of Excel that are dynamic-array aware.

     

    The SUMPRODUCT version works in all versions of Excel.

     

    I don't know why you provided the Excel data file.  If you want help with debugging your logic, you need to provide a file with data in the appropriate columns.  And you should highlight rows where you expect the logic to be true.  I don't see any column with 18 in the highlighted rows in your file.

     

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

      JoeUser2004 

       

      Thank you for your help.

       

      I apologize for not including the correct sheet name in the previous file. I have now updated the file to reflect the correct sheet name, which is HS, and the formulas are adjusted accordingly. I applied your formula to the Formula Test tab; however, it returns #value, which does not match the expected result. According to the sample data, the correct result should be -6,662.62. Additionally, I have tried another formula, which is also returning zero.

       

      Could you please provide further assistance to resolve this issue? Any additional guidance or suggestions would be greatly appreciated.

       

      Thank you for your help.

      Best regards,
      Regina

      • JoeUser2004's avatar
        JoeUser2004
        Bronze Contributor
        Jul 29, 2024

        ReginaAnn

         

        PS....  The logic of your SUM(SUMIFS(...), SUMIFS(...), ...) formula is certainly not the same as your original formula, as I corrected it. 

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    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.

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

      SergeiBaklan 

      Thank you for your prompt response.

      I apologize for not including the correct sheet name in the previous file. I have now updated the file to reflect the correct sheet name, which is HX, and the formulas are adjusted accordingly. I applied your formula to the Formula Test tab; however, it returns zero, which does not match the expected result. According to the sample data, the correct result should be -6,662.62. Additionally, I have tried another formula, which is also returning zero which is labeled Regina.

       

      Could you please provide further assistance to resolve this issue? Any additional guidance or suggestions would be greatly appreciated.

      Thank you for your help.

      Best regards,
      Regina

       

Resources