sumif?

[Dave]

I have a spreadsheet for each month (Jan, Feb etc) which shows the number of
hours a person worked on that job in a month.

Col A Job#
40 81 90 etc
A name 2.5hr 3hr
B name 15 5.5
etc

The names are in column A in each sheet and the job numbers are in row 5.

On a totals spreadsheet, I will put the names in column A and the jobs I am
interested in along row 5. I want to add up the amount of time that a person
worked on the specific job numbers I am interested in from the 12 other
sheets. There are 2 variables that need to be checked - the person's name and
the job number in the 12 sheets and the names are not always in the same row
and the job numbers can differ month to month. I thought of using sumif but
was not sure how to work with both variables.

What is the best way to do this?

Thanks.

 

[JMB]

You coult use sumproduct. Suppose your names are in A6:A10, job numbers are
in B5:F5 and the hours are in B6:F10.

=SUMPRODUCT((B6:F10)*(A6:A10="Name")*(B5:F5=JobNumber))

 

[Dave]

JMB, thanks - this works and I can use it. I set it up as follows where
person name = b$5 and $a9 =job number. I would then use this for the 12
sheets. Am I doing this correctly or should I be doing this more elegantly?

=SUMPRODUCT(('July06 Totals'!$B$9:$CE$81)*('July06
Totals'!$A$9:$A$81=$A9)*('July06 Totals'!$B$5:$CE$5=B$5))+SUMPRODUCT(('Aug06
Totals'!$B$9:$CE$81)*('Aug06 Totals'!$A$9:$A$81=$A9)*('Aug06
Totals'!$B$5:$CE$5=B$5)) +sumproduct for other 10 sheets in the same way

For my own interest: Since the list of names and jobs can change month to
month I thought I could select the whole column 'Aug06 Totals'!A:A=$A9 and
row respectively or broaden the range a lot but this gave me an error or a
#value problem - I assume because in many sheets those cells were blank.

 

[JMB]

Can't think of a shorter/better way to do it off the top of my head other
than to use it on 12 sheets.

You can't use A:A or A1:A65536 w/sumproduct due to XL's limitations (prior
to XL2007), but you can use A1:A65535 or A2:A65536.

Or, I'll add gridlines to the table and color the bottom row and add a
comment to insert additional rows at that row if additional space is needed.
So when the table is expanded, the formulas will update to recognize the
additional rows. For example, the formula references A1:A12 (row 12 is my
designated insertion point), when rows are inserted at row 12, the formula
updates itself to A1:A13 and so on.

Depending on how many formulae you have, referencing a lot of empty
rows/columns could impact your recalculation speed.

 

[Dave]

Thanks for the help!

Can't think of a shorter/better way to do it off the top of my head other
than to use it on 12 sheets.

You can't use A:A or A1:A65536 w/sumproduct due to XL's limitations (prior
to XL2007), but you can use A1:A65535 or A2:A65536.

Or, I'll add gridlines to the table and color the bottom row and add a
comment to insert additional rows at that row if additional space is needed.
So when the table is expanded, the formulas will update to recognize the
additional rows. For example, the formula references A1:A12 (row 12 is my
designated insertion point), when rows are inserted at row 12, the formula
updates itself to A1:A13 and so on.

Depending on how many formulae you have, referencing a lot of empty
rows/columns could impact your recalculation speed.