Grouping dates on a chart

  • Thread starter Thread starter Duncs
  • Start date Start date
D

Duncs

I have a workbook with 12 sheets in it, one for each month of the
year. Each sheet has the day of the week in column B, several other
values in columns C -- J and a transaction value in column K. What I
want to do is create a chart that groups the dates into days of the
week and then displays a bar showing the sum of all transactions on
each of those days in the month. So, for example, the sheet for
March
would show:

1 x x x x x x x x 1250.00
2 x x x x x x x x 80.00
3 x x x x x x x x 3000.00
4 x x x x x x x x 5250.00
..
..
..
30 x x x x x x x x 150.00
31 x x x x x x x x 100.00


So, based on the values above, the chart should show 7 bars with the
values as follows:


Monday 1250.00
Tuesday 230.00
Wednesday 3100.00
Thursday 5250.00
Friday 0.00
Saturday 0.00
Sunday 0.00

How do I achieve this?

TIA

Duncs
 
You will need to first create a sum of all your data. On each sheet, setup a
range (in the same spot, lets say AA1:AB7)
List the days of the week, and in AB1:AB7, do:
=SUMIF(A:A,AB1,K:K)
Copied down.

Use this as the data for your plot.
 
Jon,

Unfortunately, I tried a Pivot Table and it wont let me get to the
level of detail that I need. I need the report to show me a sum for
all Monday's, Tuesday's etc. in the month. The Pivot Table doesn't,
AFAIK, let me get to that level of detail.

Duncs
 
Luke,

Cheers for that. Works great.

Duncs

You will need to first create a sum of all your data. On each sheet, setup a
range (in the same spot, lets say AA1:AB7)
List the days of the week, and in AB1:AB7, do:
=SUMIF(A:A,AB1,K:K)
Copied down.

Use this as the data for your plot.

--
Best Regards,










- Show quoted text -
Click to expand...
 
I think I've done that with a dummy column that contains the name of the
days of the week.

- Jon
 
Back
Top