[Warne57032]

Hi...I'm working on a spreadsheet in which I am trying to sum the hours worked by employee, by activity. So basically, how many hours has a particular employee spent on a particular.

I've attached an example of the spreadsheet from which I'm working. Please help

Thanks,

Mario

[slurpee55]

Well, this is a bit of a cheat, but it will work...
Insert another column before the the hours column - it will just be used for the purposes of the formula, so it can be hidden later.
Assuming the names are the same as those listed at the bottom and that they will appear in column B (under professional) enter this into the top cell of the new column:
=(B2&E2)
which just combines the name and the job.
In cell B61, enter this:
=IF(ISERROR(VLOOKUP(B$60&$A61,$F$2:$G$9,2,0)),"",VLOOKUP(B$60&$A61,$F$2:$G$ 9,2,0))
and drag down the cells.
(In C61 you would enter =IF(ISERROR(VLOOKUP(C$60&$A61,$F$2:$G$9,2,0)),"",VLOOKUP(C$60&$A61,$F$2:$G$ 9,2,0))
- note that the B$60 changes to C$60...you can drag the formula across and it will change correctly)

[slurpee55]

If you prefer, you can alter this part
=IF(ISERROR(VLOOKUP(B$60&$A61,$F$2:$G$9,2,0)),""
to
=IF(ISERROR(VLOOKUP(B$60&$A61,$F$2:$G$9,2,0)),0
and have zeros in the cells rather than blanks.

[bomb #21]

Your formula's off, somewhat. In B61,

=SUMPRODUCT(($B$2:$B$51=B$60)*($E$2:$E$51=$A61)*($F$2:$F$51))

Copy across, then down.

[edhicks]

Attached is a quick pass at your question.

[slurpee55]

edhicks, your's is a bit off. But bomb_21's works nicely and doesn't need the additional column that mine does.

[edhicks]

Slurpee55 I'm not arguing, but can you indicate where I'm off. I haven't been able to make other two solutions work for me. Cheers & keep up the good work

Correction I have now made 2nd solution work and only difference appears to be I reordered the activities to alphabetical list


"I never make mistakes. I thought i made one the other day but I was wrong."

[slurpee55]

I think the difference is that you assigned names "A Employee", "B Employee" in the cells above the current names in row 60, whereas bomb and I replaced the words "A Employee", "B Employee" in column B2:B9, etc with the names that were given in row 60. Thus, both of our formulas look for the names in B60, C60, etc.

[Warne57032]

Thanks to you all: slurpee55, bomb #21, and edhicks. I've learned from something new from each of you. Thanks for helping me partially resolve this issue. I may be back soon. The hours may be broken down into days. So I'll need to do this same thing but by employee x by day x by activity.

[edhicks]

Slurpee55 That works for me. Now to hear from Warne57032. Is this question solved?

[slurpee55]

Come back if you need a hand Warne.

[Warne57032]

This one is solved.