SUMPRODUCT

[Guest]

I have several columns that I need to summarize data that meet certain
criteria. The idea is to summarize labor hours by Job Number by Date.
Example of Data:

Col 1 Col 2 Col 3
Col 4
Job # Employee Name WE Date # Hours
Job1 Don Duck 10/27/06 14
Job2 Mike Mouse 9/29/06 40
Job3 Joe Blow 11/3/06 16
Job1 Tom Terrific 9/22/06 36
Job1 Sylvester Katz 8/26/06 20
Job2 Don Duck 10/27/06 26

Example of Desired output:
Aug 06 Sep 06 Oct 06 Nov 06
Job1 xx Hrs xx Hrs xx Hrs
xx Hrs
Job2 xx Hrs xx Hrs xx Hrs
xx Hrs
Job3 xx Hrs xx Hrs xx Hrs
xx Hrs

I want to set parameters such that if the hours worked fall between the
beginning of a month and the end of that same month they are summed in that
month column. My example is, of course, a much simplified version of a data
base with some 13 thousand lines.

Any suggestions will be appreciated.
Thanks in advance.

 

[vezerid]

Billy,

I am assuming the desired output has A1 blank and starts with headers
in B1 and to the right and jobs are in A2 and below. Also, assume
source data in Sheet1. The following formula can be placed in B2 and
copied down and across.

=SUMPRODUCT(('Sheet1'!$D$2:$D$200)*('Sheet1'!$A$2:$A$200=$A2)*(TEXT('Sheet1'!$C$2:$C$200,"d
mmm yy")=TEXT(--("1 "&B$1),"d mmm yy")))

Note that for this formula to work you need to have your headings as
specified, e.g. Aug 06.

HTH
Kostis Vezerides

 

[Marc]

You probably should use a pivot table...

 

[Guest]

Kostis, thanks but, the column headings are actually formatted month-end
dates not text. The routine needs to evaluate the weekending date in the
database as to whether it is > the prior monthend and <= to the current
monthend. Once a record is categorized as being relative to a specified
month I want to sum all hours for each of the job numbers by month.

 

[vezerid]

No problem. Use the following variant:

=SUMPRODUCT(('Sheet1'!$D$2:$D$200)*('Sheet1'!$A$2:$A$200=$A2)*(TEXT('Sheet1'!$C$2:$C$200,"mmm
yy")=TEXT(B$1,"mmm yy")))

HTH
Kostis

 

[Marc]

Sum of hours Month
Job # Aug Sep Oct Nov Grand Total
1 20 36 14 70
2 40 26 66
3 16 16
Grand Total 20 76 40 16 152


The above is a pivot table result from your data. It can automatically group
all dates into months... All you need to do is select all your data
including the header row of labels (do not select the entire column, only
the actual data), then select "data/pivot table" and drag and drop the
column labels to create the table. Then right click on the month headings
(which will at first display the actual dates), and select "group and show
detail", then select "Group", and pick "Months". It will then group all
those separate dates into months...

It only takes about 30 seconds to do the whole thing... and in the end is a
much more flexible way to produce what you want...