Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 part question on averaging | Excel Discussion (Misc queries) | |||
Sumproduct ... Empty Cells vs Spaces? | Excel Discussion (Misc queries) | |||
adding two sumproduct formulas together | Excel Worksheet Functions | |||
Sumproduct in Excel Spreadsheet to read Access db table | Excel Worksheet Functions | |||
SUMPRODUCT using offset from ROW if X marks the spot | Excel Worksheet Functions |