Daniel,

Try this

=SUMPRODUCT((INDIRECT("rPerson"&A1)="John
Doe")*(INDIRECT("rYear"&A1)=B$1),INDIRECT("rSales" &A1))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Daniel Bonallack" wrote in
message ...
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