Use Sum() with variable range

[Jeff]

Hi, I have a sales report which needs to sum year-to-date sales by state.
Right now, the information is sitting in a pivot table linked to a live
database... so while my sales report may be for February, the pivot table is
also reporting sales up to the current date (which is often a week or two
into the next month). That means I cannot use the "sales total" reported in
the pivot table... because it will include sales for the current month which
is not part of the report. So what I'm doing now, is linking a "table" to
the pivot report via the SUM() formula. Every month I drag the sum formula
down one more row for EACH STATE! Is there not a way that I could have the
argument of the SUM() function defined such that if the month is March, then
the summed range is defined as the first cell in the range + the next two
cells below... e.g., I could have a table with each month equal to an integer
(Jan, 0, Feb 1, Mar, 2, Apr, 3) and perhaps use VLOOKUP() to pull in the
correct integer by which to grow the range. This may be confusing. I'm
sorry if it is. If there is an entirely easier way to do this please let me
know that as well.

AND the reason I'm not just going into the Pivot Table to restrict it to the
current month is that there are 3 pivot tables per report and 4 reports (one
for each sale representative... and growing)... and I don't want to have to
go in and change each pivot table each month... This is also the first set of
several reports that I'm working on... and I'm trying to make them as
automatic as I can.

 

[Francis]

does SUMPRODUCT do what you want.
the syntax :
=SUMPRODUCT(--(A2:A30="State"),--(B2:B30="Month"),C2:C30)

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[Bob Phillips]

If each pivot has the same field names and is to show the same single month,
you could put an event monitor on the selected date cell in the first pivot,
then change the other pivots to have the same values.

 

[Jeff]

Hi Bob, thanks for the suggestion, but I'm not sure I understand what an
"event monitor" is. Would you be able to explain? Is that a macro function?

Also, the pivot report isn't showing just a single month... it looks like
this:
2007 2008 2009
State1
Jan xxx.xx xxx.xx xxx.xx
Feb xxx.xx xxx.xx xxx.xx
Mar xxx.xx xxx.xx xxx.xx
... to December

State2
Jan xxx.xx xxx.xx xxx.xx
Feb... to December

I have five states. So, say the month is March. (That's the report I'm
working on right now.) I need to be able to report comparative monthly sales
by state (e.g., March of 2007, '08, '09), and YTD '07, '08, '09.

So, what I'm doing is summing Jan, Feb, & Mar... for '07, '08, & '09 FOR
EACH STATE (for the YTD report). I use that to create a table and a chart
for a dashboard. So I drag the SUM() formula for each year and each state
down one more row (15 times) (this is just one section of the dashboard).

I hope this clarifies...

 

[Jeff]

Hi Francis, and thanks for the response. That might work, and looks very
interesting... what are the "--" arguments???? But, I'm not really wanting
to multiply across columns.

Below is the pivot table:

Column A.....Column B..... Column C...... Column D.... Column E
State............Month............2007 sales....2008 sales...2009 sales

So, the first twelve (12) rows = state 1
.......the second 12 rows = state 2.... and so on.

So, I need to generate two tables from this data:
1st - month comparison year on year (i.e., march of '07, '08, & '09)
2nd - YTD comparison year on year (i.e., YTD '07, '08, & '09)

So, for this second table, every month, I'm having to add one more month to
the SUM() function to get the YTD sales:
for March 2007 YTD the formula would be, SUM(C1:C3)
for April 2007 YTD the formula would be, SUM(C1:C4)
for May 2007 YTD the formula would be, SUM(C1:C5) and so on.

I have to repeat this for 2008, and 2009 (columns D and E) and I have to
repeat it for each state. I'm not sure if this helps or if this makes it
more confusing... thanks again.