SUMPRODUCT question

[Daniel Bonallack]

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

 

[Barb]

Hi Daniel
How about if you had one sheet with all that data plus one more column for
"State".Then you can just run a pivot table against it. It would give you
much more flexibility with any reports.

Barb

 

[Daniel Bonallack]

A good suggestion, that made me realize I'd forgotten to mention and
important piece of information. Each worksheet has about 30,000 rows of
data. No chance to bring them together.

Daniel

 

[Bob Phillips]

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]

Bob, thanks a lot.

Daniel

Hi Daniel
How about if you had one sheet with all that data plus one more column for
"State".Then you can just run a pivot table against it. It would give you
much more flexibility with any reports.

Barb