sumproduct on range containing vlookup formula

[dnessim]

Hi,

Rate_Base is a named range with empty cells, when I put values in the cell it correctly sums the values of rate_base

=SUMPRODUCT((MONTH(Entry_Date)=1)*(YEAR(Entry_Date)=2013)*(Indicator_Name="CLABSI")*(Facility="Carrollton")*(Unit_Group="ICU")*(Rate_Base))


Why does this not work?

If I put a vlookup formula in the range rate_base I get a #VALUE error.

The formula in rate_base is

=IF(A2<>"Select - Indicator",VLOOKUP(A2,CategoryLookup,4,FALSE),"")


Thanks

Dave

 

[Haseeb A]

Hello Dave,


If A2="Select - Indicator" the output will be "" in Rate_Base range, so SUMPRODUCT multiplying with text entries.


Just change last part form:


*(Rate_Base) to


,Rate_Base


Note: There is a comma before Rate_Base instead of *. SO the final formula should be,


=SUMPRODUCT((MONTH(Entry_Date)=1)*(YEAR(Entry_Date)=2013)*(Indicator_Name="CLABSI")*(Facility="Carrollton")*(Unit_Group="ICU"),Rate_Base)


Also you could reduce MONTH & YEAR condition with TEXT.


Hope this helps;

Haseeb

 

[dnessim]

Hi Hasseb,

Thank you. That fixed my problem.

Dave

 

[dnessim]

Hi Hasseb,

Thank you. That fixed my problem.

Dave