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.

--
Best Regards,

Luke M
"Duncs" wrote in message
...
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

The easiest way to apply this kind of grouping is with a pivot table.

Grouping by Date in a Pivot Table
http://peltiertech.com/WordPress/gro...a-pivot-table/

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 3/29/2010 9:29 AM, Luke M wrote:
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

On 29 Mar, 18:36, Jon Peltier wrote:
The easiest way to apply this kind of grouping is with a pivot table.

Grouping by Date in a Pivot Tablehttp://peltiertech.com/WordPress/grouping-by-date-in-a-pivot-table/

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.http://peltiertech.com/

On 3/29/2010 9:29 AM, Luke M wrote:



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.- Hide quoted text -

- Show quoted text -

Luke,

Cheers for that. Works great.

Duncs

On 29 Mar, 14:29, "Luke M" wrote:
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,

Luke M"Duncs" wrote in message

...



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- Hide quoted text -

- Show quoted text -

I think I've done that with a dummy column that contains the name of the
days of the week.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 3/30/2010 3:48 AM, Duncs wrote:
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

On 29 Mar, 18:36, Jon wrote:
The easiest way to apply this kind of grouping is with a pivot table.

Grouping by Date in a Pivot Tablehttp://peltiertech.com/WordPress/grouping-by-date-in-a-pivot-table/

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.http://peltiertech.com/

On 3/29/2010 9:29 AM, Luke M wrote:



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.- Hide quoted text -

- Show quoted text -