Announcement

Collapse
No announcement yet.

SUMPRODUCT Value Error

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • SUMPRODUCT Value Error



    Hello, my first post here. The forum has been very helpful in my work, and I've come across a question that I can't seem to answer.

    I am getting a #VALUE error on a SUMPRODUCT function and cannot determine the cause.

    In my first sheet titled "Results", I have the criteria, and the data is in the 2nd sheet titled "Data".

    I have the following formula in the Results Sheet.

    =SUMPRODUCT((Data!$A:$A=Results!$C$5)*(Data!$B:$B=Results!$C$6)*Data!$C:$C)

    I would like to sum up the Units (column C of Data tab) where the Region (column A of data tab) = C5 of Results tab AND Channel (column B of data tab) = C6 of Results tab.

    Thank you very much!

  • #2
    Re: SUMPRODUCT Value Error

    It might be because you have a header row with text in it....

    Try:

    =SUMPRODUCT((Data!$A:$A=Results!$C$5)*(Data!$B:$B=Results!$C$6),Data!$C:$C)

    also, with Sumproduct, it is recommended not to use whole column references since they are not efficient (and it won't work anyway in Excel 2003 or earlier).

    Instead an equivalent and more efficient formula for Excel 2007 or later is:

    =SUMIFS(Data!$C:$C,Data!$A:$A,Results!$C$5,Data!$B:$B,Results!$C$6)
    Where there is a will there are many ways. Finding one that works for you is the challenge!

    MS Excel MVP 2010-2016

    Comment


    • #3


      Re: SUMPRODUCT Value Error

      Thank you, NBVC! It was the header row, that fixed it. I have Excel 2007, so the SUMIFS would be better for the specific question I asked. For further details of what I am trying to do, I have 5 cells containing data validation that correspond to columns on my Data tab. In the data validation list, the user can choose 1 item or all items. If they choose "All Items", that would not have any results within the data, but what I would really want the formula to do is to 'skip' that criteria and move to the next, in essence including all the items for the given column. So, I have just been playing around trying to figure this out, I started out with SUMIFS and tried to nest with IF(ISNA(VLOOKUP(, which I could get to work if there were only 2 conditions, but with 5 conditions, there were too many possible combinations, and I gave up. I was thinking I might could make it work with the SUMPRODUCT function, but I started with an easier 2 criteria formula to troubleshoot my #VALUE error.

      Thanks!!

      Comment

      Working...
      X