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.

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:A101>3)*--(A2:A101<10)*(C2:J101))

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

Does this help?
Kostis Vezerides

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" <[email protected]> wrote in message
news:%[email protected]...
> 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.
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
>> 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.
>>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>>> 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.
>>>
>>
>>
>
>

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

"Roger Govier" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:%[email protected]...
>>>> 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.
>>>>
>>>
>>>
>>
>>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> Roger - your suggestions seemed to have worked-out well (even without
> the "double unary minuses"!) - many thanks for your help.
>
> "Roger Govier" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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" <[email protected]> wrote in message
>>>> news:%[email protected]...
>>>>> 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.
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

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

--
Regards

Roger Govier


"Roger Govier" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> Roger - your suggestions seemed to have worked-out well (even without
>> the "double unary minuses"!) - many thanks for your help.
>>
>> "Roger Govier" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>> 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" <[email protected]> wrote in message
>>>>> news:%[email protected]...
>>>>>> 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.
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>