HI guys,

I have a formula that currently works and is in the following format:

SUM(‘A_Worksheet’!$D82:$U82)/INDEX(Named_Range1,MATCH(Type_of_money,Named_Range_of_money_types,0),Match(Specific_Year,Named_Range_of_Years)

What I need to do is to modify the formula so that each of the items in A_Worksheet is divided by the variable from the same year, rather than the specific year that it currently is, and then summed. I thought that I could achieve the desired result by using SUMPRODUCT and inverting the denominator. This seemed to work for a simple test case, but when I apply it here, I get something like this…

SUMPRODUCT((‘A_Worksheet’!$D82:$U82)/INDEX(Named_Range1,MATCH(Type_of_money,Named_Range_of_money_types,0),Match(‘A_Worksheet’!$D80:$U80,Named_Range_of_Years))

Unfortunately, the denominator evaluates as #VALUE!, and I’m not sure how to fix it.

Any suggestions would be most appreciated.

Thanks!!

Roy