I have 50 worksheets (one for each US state). Each has three columns -
salesperson, year and sales.

On a new tab, I have in A2:A51 the 50 states. In cells B1:E1 I have the
years 2001, 2002, 2003, 2004.

I want to know the sales, per year for John Doe, in each of the states. I
have range names set up in each of the 50 tabs (done with a macro).

In cell B2 I have the formula =SUMPRODUCT((rPersonAlaska="John
Doe")*(rYearAlaska=B$1)*rSalesAlaska)

This of course works for Alaska, but how can I make the formula cleverly
refer to the state names in column A?

Sorry this is complicated - thanks for any help

Daniel