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.