Compiling Data
 

Can anyone suggest a way of doing this ...

I have a file detailing the results of 10 Area Managers. Each AM has a
Sheet - column A lists weekdays throughout the year and C - J represent 8
product groups. On a daily basis the results for each AM are entered on
his/her Sheet.

I need a summary sheet that will show the results between any given periods
during the year. I can only think of doing this using SUMPRODUCT (and =,
<= parameters) but that would seem to mean having to create 80 (ie; 10 x 8)
named ranges.

Is there a simpler way?

Many thanks.

Compiling Data
 

Terry,
what are the data in columns C:J? If they are product quantities with
different prices then, yes, you would need a more complex formula. If
they are all monetary amounts then you have no problem summing over
more than one columns. E.g.:

=SUMPRODUCT(--(A2:A1013)*--(A2:A101<10)*(C2:J101))

will include in the sum all columns from the pertinent rows.

Does this help?
Kostis Vezerides

Compiling Data
 

Hi Terry

It needn't be that complicated.
Group the sheets 2 through 10 that refer to the individual AM's, by
selecting the tab of the first one, hold down shift and press the tab of
the last one (ensure that your summary sheet is not within this
grouping.

In cell L2 of any of the grouped sheets enter
=SUMPRODUCT(--(TEXT($A$2:$A$100,"mm-yy")=TEXT(Sheet1!$A$1,"mm-yy")),B2:B100)

Change the range from 100 to 10,000 or as many rows as you are likely to
have on each sheet.
Drag the formula across through M2:S2

Click on your Summary sheet, this will ungroup the others.

In cell A4 Enter Area Manager 1, etc down to Area Manager 10 in cell A13
In cell C3 enter your product titles as per C:J from the AMSheets.

In cell C3 enter =Sheet1!L2
In cell C4 enter =Sheet2!L2 and continue down through C5:C13 stepping
up the sheet number each time.
Copy C3:C13 and copy across through D3:J3

Now, when you enter a date in cell A1 on Summary, you will have the
monthly totals for each Manager for each product.

--
Regards

Roger Govier


"Terry Bennett" wrote in message
...
Can anyone suggest a way of doing this ...

I have a file detailing the results of 10 Area Managers. Each AM has
a Sheet - column A lists weekdays throughout the year and C - J
represent 8 product groups. On a daily basis the results for each AM
are entered on his/her Sheet.

I need a summary sheet that will show the results between any given
periods during the year. I can only think of doing this using
SUMPRODUCT (and =, <= parameters) but that would seem to mean having
to create 80 (ie; 10 x 8) named ranges.

Is there a simpler way?

Many thanks.

Compiling Data
 

Thanks for the suggestions guys - I will have a play-around and see how I
get on!

Only thing I don't follow are the "--" symbols within the SUMPRODUCT
formula. What effect do these have?

"Roger Govier" wrote in message
...
Hi Terry

It needn't be that complicated.
Group the sheets 2 through 10 that refer to the individual AM's, by
selecting the tab of the first one, hold down shift and press the tab of
the last one (ensure that your summary sheet is not within this grouping.

In cell L2 of any of the grouped sheets enter
=SUMPRODUCT(--(TEXT($A$2:$A$100,"mm-yy")=TEXT(Sheet1!$A$1,"mm-yy")),B2:B100)

Change the range from 100 to 10,000 or as many rows as you are likely to
have on each sheet.
Drag the formula across through M2:S2

Click on your Summary sheet, this will ungroup the others.

In cell A4 Enter Area Manager 1, etc down to Area Manager 10 in cell A13
In cell C3 enter your product titles as per C:J from the AMSheets.

In cell C3 enter =Sheet1!L2
In cell C4 enter =Sheet2!L2 and continue down through C5:C13 stepping up
the sheet number each time.
Copy C3:C13 and copy across through D3:J3

Now, when you enter a date in cell A1 on Summary, you will have the
monthly totals for each Manager for each product.

--
Regards

Roger Govier


"Terry Bennett" wrote in message
...
Can anyone suggest a way of doing this ...

I have a file detailing the results of 10 Area Managers. Each AM has a
Sheet - column A lists weekdays throughout the year and C - J represent 8
product groups. On a daily basis the results for each AM are entered on
his/her Sheet.

I need a summary sheet that will show the results between any given
periods during the year. I can only think of doing this using SUMPRODUCT
(and =, <= parameters) but that would seem to mean having to create 80
(ie; 10 x 8) named ranges.

Is there a simpler way?

Many thanks.

Compiling Data
 

Hi Terry

The "--" signs are the double unary minus.
They are there to coerce the results of the comparisons (which would
return True or False) into 1's for True and 0's for False so that
Sumproduct has an array of numbers to deal with.

The same effect could be achieved by multiplying by 1, but the use of
the "--" is thought to be slightly more efficient in calculation terms.
If you want to read more about this, then Bob Phillips has an excellent
treatise on the whole Sumproduct function at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards

Roger Govier


"Terry Bennett" wrote in message
...
Thanks for the suggestions guys - I will have a play-around and see
how I get on!

Only thing I don't follow are the "--" symbols within the SUMPRODUCT
formula. What effect do these have?

"Roger Govier" wrote in message
...
Hi Terry

It needn't be that complicated.
Group the sheets 2 through 10 that refer to the individual AM's, by
selecting the tab of the first one, hold down shift and press the tab
of the last one (ensure that your summary sheet is not within this
grouping.

In cell L2 of any of the grouped sheets enter
=SUMPRODUCT(--(TEXT($A$2:$A$100,"mm-yy")=TEXT(Sheet1!$A$1,"mm-yy")),B2:B100)

Change the range from 100 to 10,000 or as many rows as you are likely
to have on each sheet.
Drag the formula across through M2:S2

Click on your Summary sheet, this will ungroup the others.

In cell A4 Enter Area Manager 1, etc down to Area Manager 10 in cell
A13
In cell C3 enter your product titles as per C:J from the AMSheets.

In cell C3 enter =Sheet1!L2
In cell C4 enter =Sheet2!L2 and continue down through C5:C13
stepping up the sheet number each time.
Copy C3:C13 and copy across through D3:J3

Now, when you enter a date in cell A1 on Summary, you will have the
monthly totals for each Manager for each product.

--
Regards

Roger Govier


"Terry Bennett" wrote in message
...
Can anyone suggest a way of doing this ...

I have a file detailing the results of 10 Area Managers. Each AM
has a Sheet - column A lists weekdays throughout the year and C - J
represent 8 product groups. On a daily basis the results for each
AM are entered on his/her Sheet.

I need a summary sheet that will show the results between any given
periods during the year. I can only think of doing this using
SUMPRODUCT (and =, <= parameters) but that would seem to mean
having to create 80 (ie; 10 x 8) named ranges.

Is there a simpler way?

Many thanks.

Compiling Data
 

Roger - your suggestions seemed to have worked-out well (even without the
"double unary minuses"!) - many thanks for your help.

"Roger Govier" wrote in message
...
Hi Terry

The "--" signs are the double unary minus.
They are there to coerce the results of the comparisons (which would
return True or False) into 1's for True and 0's for False so that
Sumproduct has an array of numbers to deal with.

The same effect could be achieved by multiplying by 1, but the use of the
"--" is thought to be slightly more efficient in calculation terms.
If you want to read more about this, then Bob Phillips has an excellent
treatise on the whole Sumproduct function at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards

Roger Govier


"Terry Bennett" wrote in message
...
Thanks for the suggestions guys - I will have a play-around and see how I
get on!

Only thing I don't follow are the "--" symbols within the SUMPRODUCT
formula. What effect do these have?

"Roger Govier" wrote in message
...
Hi Terry

It needn't be that complicated.
Group the sheets 2 through 10 that refer to the individual AM's, by
selecting the tab of the first one, hold down shift and press the tab of
the last one (ensure that your summary sheet is not within this
grouping.

In cell L2 of any of the grouped sheets enter
=SUMPRODUCT(--(TEXT($A$2:$A$100,"mm-yy")=TEXT(Sheet1!$A$1,"mm-yy")),B2:B100)

Change the range from 100 to 10,000 or as many rows as you are likely to
have on each sheet.
Drag the formula across through M2:S2

Click on your Summary sheet, this will ungroup the others.

In cell A4 Enter Area Manager 1, etc down to Area Manager 10 in cell A13
In cell C3 enter your product titles as per C:J from the AMSheets.

In cell C3 enter =Sheet1!L2
In cell C4 enter =Sheet2!L2 and continue down through C5:C13 stepping
up the sheet number each time.
Copy C3:C13 and copy across through D3:J3

Now, when you enter a date in cell A1 on Summary, you will have the
monthly totals for each Manager for each product.

--
Regards

Roger Govier


"Terry Bennett" wrote in message
...
Can anyone suggest a way of doing this ...

I have a file detailing the results of 10 Area Managers. Each AM has a
Sheet - column A lists weekdays throughout the year and C - J represent
8 product groups. On a daily basis the results for each AM are entered
on his/her Sheet.

I need a summary sheet that will show the results between any given
periods during the year. I can only think of doing this using
SUMPRODUCT (and =, <= parameters) but that would seem to mean having
to create 80 (ie; 10 x 8) named ranges.

Is there a simpler way?

Many thanks.

Compiling Data
 

Hi Terry

Thanks for the feedback. Glad it worked out for you.
As it happens, in another thread in excel.misc, I have experienced a
problem for the first time using the -- notation, which returned a
#VALUE result, where using the 8 to multiply the matrices produced the
correct result.
I am going to have to re-visit this whole concept again and decide which
way I prefer to operate in the future.

--
Regards

Roger Govier


"Terry Bennett" wrote in message
...
Roger - your suggestions seemed to have worked-out well (even without
the "double unary minuses"!) - many thanks for your help.

"Roger Govier" wrote in message
...
Hi Terry

The "--" signs are the double unary minus.
They are there to coerce the results of the comparisons (which would
return True or False) into 1's for True and 0's for False so that
Sumproduct has an array of numbers to deal with.

The same effect could be achieved by multiplying by 1, but the use of
the "--" is thought to be slightly more efficient in calculation
terms.
If you want to read more about this, then Bob Phillips has an
excellent treatise on the whole Sumproduct function at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards

Roger Govier


"Terry Bennett" wrote in message
...
Thanks for the suggestions guys - I will have a play-around and see
how I get on!

Only thing I don't follow are the "--" symbols within the SUMPRODUCT
formula. What effect do these have?

"Roger Govier" wrote in message
...
Hi Terry

It needn't be that complicated.
Group the sheets 2 through 10 that refer to the individual AM's, by
selecting the tab of the first one, hold down shift and press the
tab of the last one (ensure that your summary sheet is not within
this grouping.

In cell L2 of any of the grouped sheets enter
=SUMPRODUCT(--(TEXT($A$2:$A$100,"mm-yy")=TEXT(Sheet1!$A$1,"mm-yy")),B2:B100)

Change the range from 100 to 10,000 or as many rows as you are
likely to have on each sheet.
Drag the formula across through M2:S2

Click on your Summary sheet, this will ungroup the others.

In cell A4 Enter Area Manager 1, etc down to Area Manager 10 in
cell A13
In cell C3 enter your product titles as per C:J from the AMSheets.

In cell C3 enter =Sheet1!L2
In cell C4 enter =Sheet2!L2 and continue down through C5:C13
stepping up the sheet number each time.
Copy C3:C13 and copy across through D3:J3

Now, when you enter a date in cell A1 on Summary, you will have the
monthly totals for each Manager for each product.

--
Regards

Roger Govier


"Terry Bennett" wrote in message
...
Can anyone suggest a way of doing this ...

I have a file detailing the results of 10 Area Managers. Each AM
has a Sheet - column A lists weekdays throughout the year and C -
J represent 8 product groups. On a daily basis the results for
each AM are entered on his/her Sheet.

I need a summary sheet that will show the results between any
given periods during the year. I can only think of doing this
using SUMPRODUCT (and =, <= parameters) but that would seem to
mean having to create 80 (ie; 10 x 8) named ranges.

Is there a simpler way?

Many thanks.

Compiling Data
 

Typo
using the 8 to multiply
should of course read
using the * to multiply

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
Hi Terry

Thanks for the feedback. Glad it worked out for you.
As it happens, in another thread in excel.misc, I have experienced a
problem for the first time using the -- notation, which returned a
#VALUE result, where using the 8 to multiply the matrices produced the
correct result.
I am going to have to re-visit this whole concept again and decide
which way I prefer to operate in the future.

--
Regards

Roger Govier


"Terry Bennett" wrote in message
...
Roger - your suggestions seemed to have worked-out well (even without
the "double unary minuses"!) - many thanks for your help.

"Roger Govier" wrote in message
...
Hi Terry

The "--" signs are the double unary minus.
They are there to coerce the results of the comparisons (which would
return True or False) into 1's for True and 0's for False so that
Sumproduct has an array of numbers to deal with.

The same effect could be achieved by multiplying by 1, but the use
of the "--" is thought to be slightly more efficient in calculation
terms.
If you want to read more about this, then Bob Phillips has an
excellent treatise on the whole Sumproduct function at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards

Roger Govier


"Terry Bennett" wrote in message
...
Thanks for the suggestions guys - I will have a play-around and see
how I get on!

Only thing I don't follow are the "--" symbols within the
SUMPRODUCT formula. What effect do these have?

"Roger Govier" wrote in message
...
Hi Terry

It needn't be that complicated.
Group the sheets 2 through 10 that refer to the individual AM's,
by selecting the tab of the first one, hold down shift and press
the tab of the last one (ensure that your summary sheet is not
within this grouping.

In cell L2 of any of the grouped sheets enter
=SUMPRODUCT(--(TEXT($A$2:$A$100,"mm-yy")=TEXT(Sheet1!$A$1,"mm-yy")),B2:B100)

Change the range from 100 to 10,000 or as many rows as you are
likely to have on each sheet.
Drag the formula across through M2:S2

Click on your Summary sheet, this will ungroup the others.

In cell A4 Enter Area Manager 1, etc down to Area Manager 10 in
cell A13
In cell C3 enter your product titles as per C:J from the AMSheets.

In cell C3 enter =Sheet1!L2
In cell C4 enter =Sheet2!L2 and continue down through C5:C13
stepping up the sheet number each time.
Copy C3:C13 and copy across through D3:J3

Now, when you enter a date in cell A1 on Summary, you will have
the monthly totals for each Manager for each product.

--
Regards

Roger Govier


"Terry Bennett" wrote in message
...
Can anyone suggest a way of doing this ...

I have a file detailing the results of 10 Area Managers. Each AM
has a Sheet - column A lists weekdays throughout the year and C -
J represent 8 product groups. On a daily basis the results for
each AM are entered on his/her Sheet.

I need a summary sheet that will show the results between any
given periods during the year. I can only think of doing this
using SUMPRODUCT (and =, <= parameters) but that would seem to
mean having to create 80 (ie; 10 x 8) named ranges.

Is there a simpler way?

Many thanks.