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
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