SumProduct and Sumif

  • G'Day everyone:

    Sorry I know there are numerous posting on this topic however I have searched them and I am still having problems. I am trying to sumif with two conditions which I understand is not possible so I am using sumproduct.

    I am trying to compute the revenue for several project numbers within a certain month. For example, I have project numbers 171610, 189430, and 192310 I want to find out how much revenue was recongized within the month of April.

    Here are a couple formulas that I have been using:

    =SUMPRODUCT(('Actual Rev'!B2:B7776=I7)*('Actual Rev'!Q2:Q7776='Summary Page'!A8)*('Actual Rev'!Q2:Q7776='Summary Page'!A9)*('Actual Rev'!Q2:Q7776='Summary Page'!A10)*('Actual Rev'!Q2:Q7776='Summary Page'!A11)*('Actual Rev'!Q2:Q7776='Summary Page'!A12)*('Actual Rev'!Q2:Q7776='Summary Page'!A13)*('Actual Rev'!Q2:Q7776='Summary Page'!A14)*('Actual Rev'!Q2:Q7776='Summary Page'!A15)*('Actual Rev'!U2:U7776))

    Where I7 equals the month and Summary Page!A8 equals the project number. This returns a zero.

    If I seperate by Project for example everything seems to work fine. I have also tried to define the project numbers but this returns #N/A.

    What am I doing wrong? I am new to arrays but from the other posts I can get those examples right I just can't get the formulea to work right on my data. Thanks for the help in advance.


  • You are getting the zero because an entry in Q2:Q7776 can't simulatenously match all of the values in the summary page A8:A15. The easiest fix is to break it down by each project number and then add the parts together.

  • Try the following...

    =SUMPRODUCT(('Actual Rev'!B2:B7776=I7)*('Actual Rev'!Q2:Q7776={'Summary Page'!A8,'Summary Page'!A9,'Summary Page'!A10,'Summary Page'!A11,'Summary Page'!A12,'Summary Page'!A13,'Summary Page'!A14,'Summary Page'!A15})*('Actual Rev'!U2:U7776))

    Hope this helps!