Using Sumproduct

[amcather]

I am currently using the SUMPRODUCT to sum a value if it is in two date ranges. I want to sum a group of numbers when they don't = "N/A". We can use a previous column where N/A maps to 0. I have been using the following formulas but I get a #Value. When i have broken it out to

R= column with N/A, Q = column with 0
=SUMPRODUCT(--('7.1 - IPPS Review'!$R$2:$R$10002),--('7.1 - IPPS Review'!$B$2:$B$10002>=Start_Date),--('7.1 - IPPS Review'!$B$2:$B$10002<=End_Date),--('7.1 - IPPS Review'!Q2:Q10002<>"0"))

=SUMPRODUCT(--('7.1 - IPPS Review'!$R$2:$R$10002<>"N/A"),--('7.1 - IPPS Review'!$B$2:$B$10002>=Start_Date),--('7.1 - IPPS Review'!$B$2:$B$10002<=End_Date),
)

Thanks for your help in Advance

 

[Seti]

Try this.

In your 1st example, change "0" to just 0.

 

[Andrew Poulsom]

Try:

=SUMPRODUCT(--(NOT(ISNA('7.1 - IPPS Review'!$R$2:$R$10002))),--('7.1 - IPPS Review'!$B$2:$B$10002>=Start_Date),--('7.1 - IPPS Review'!$B$2:$B$10002<=End_Date),--('7.1 - IPPS Review'!Q2:Q10002<>"0"))

 

[amcather]

how to sum w/ N/A in array

Thanks for your reply. That works except it tries to sum the "n/a" how can i use that formula but but criteria not to sum "n/a". For example,

The values in colunmn Q are N/A, N/A, 0, 1, 2, 5, 7 - the sum should be 15 however i get #value. Is this feasible?

 

[Seti]

Are these #N/A or just N/A?

 

[Andrew Poulsom]

If Q2 on sheet "7.1 - IPPS Review" contains the formula:

=IF(ISNA(R2),0,R2)

copied down, you could use:

=SUMPRODUCT(--('7.1 - IPPS Review'!$B$2:$B$10002>=Start_Date),--('7.1 - IPPS Review'!$B$2:$B$10002<=End_Date),('7.1 - IPPS Review'!Q2:Q10002))

to sum the the values in column R that are not #N/A.