SUMPRODUCT question
 

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

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" wrote:

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

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

"Barb" wrote:

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" wrote:

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

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

Bob, thanks a lot.

Daniel

"Barb" wrote:

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" wrote:

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