Add this column only if these two criteria are met...help?

[Glythir]

In my workbook, daily time sheets are kept from two employees. On a third sheet, I have the job name and category (along with some other information). I want a column on this third sheet that will look at both of those daily sheets, and if the job name AND the category (in two different columns) on that sheet match the job name and category on the third sheet, I want it to add all the instances of time worked when both of those conditions are met.

I'm trying to track the time spent on any particular stage of work on each job we do. So, in job "Smith", we spent 4 hours on assembly, 2 hours on packing, etc....

Please help?

[daddylonglegs]

You can use SUMPRODUCT, e.g. this formula in sheet 3

=SUMPRODUCT(--(sheet1!A1:A100=D2),--(sheet1!B1:B100=D3),sheet1!C1:C100)+SUMPRODUCT(--(sheet2!A1:A100=D2),--(sheet2!B1:B100=D3),sheet2!C1:C100)

where column A on sheets 1 and 2 contains your names, column B the categories and column C the elapsed time

D2 and D3 in sheet3 contain the specific name and category for which you want to sum.

If your time is in time format then format the result cell as [h]:mm

[Glythir]

I'm getting a #NUM error that says "there's a problem with a number used in this formula".

What are the -- for in this formula?

[daddylonglegs]

Are you using whole column references? You can't use A:A with SUMPRODUCT, you need to pick a specific range, e.g. A1:A1000, all range sizes should be the same.

-- is a "co-ercer"

When you use something like

sheet1!A1:A100=D2

this produces an array of 100 TRUE/FALSE values, -- converts these to 1 or 0 and allows SUMPRODUCT to function as SUMPRODUCT only works with numbers. There are a number of different syntaxes which you could use, e.g. this is also commonly used but you get the same result in most cases

=SUMPRODUCT((sheet1!A1:A100=D2)*(sheet1!B1:B100=D3)*(sheet1!C1:C100))

[Glythir]

I can't believe it! It works! This is fantastic! You have no idea how much time this is going to save our acountng department! Thank you so much for your help on this!

One more question before I go skipping off in clerical bliss....since I can't use a column range, how high can I go for my specific range? I've got A1:A5000 currently, but this is an ongoing sheet, that will grow quite a bit.

[daddylonglegs]

Excel currently has 65536 rows, there's nothing to stop you using all but 1, e.g. A1:A65535 but that might make the calculations rather slow, probably better to use something that will be sufficient for some time but not too big, e.g. A1:A10000

...alternatively you could look into "dynamic ranges" whereby you can define a range in such a way that it expands automatically as you add values