About Us

Dave

Thanks for the help!

"JMB" wrote:

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" wrote:

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" wrote:

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=JobNum ber))

"Dave" wrote:

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.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A"	Harry Seymour	Excel Worksheet Functions	9	June 12th 07 10:47 PM
SUMIF ?	Paul	Excel Worksheet Functions	4	January 12th 07 02:37 PM
Embedding a Sumif in a sumif	C.Pflugrath	Excel Worksheet Functions	5	August 31st 05 07:31 PM
nested sumif or sumif with two criteria	dshigley	Excel Worksheet Functions	5	April 5th 05 03:34 AM
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function	Oscar	Excel Worksheet Functions	2	January 11th 05 11:01 PM

Menu

About Us