I think the solution I'm after should be based on sumproduct, but I'm not entirely sure so any help would be most welcome.
I have a sheet as follows (set up as a table - first row is headers):-
Job Dept Hours
100 Office 10
100 Office 5
100 Field 20
100 Manage 5
101 Office 1
101 Field 10
101 Field 20
101 Field 5
102 Office 10
On a separate sheet I use the UNIQUE function to create a unique dynamic list of column A (Job) from the first sheet. On this second sheet in col B (col A has the unique list) I want a formula to give me the total number of hours from col C on the first sheet for dept OFFICE - so in the case of Job 100 the answer would be 15. I'll then repeat the formula in col C but in this case I want to do the same BUT add the hours together for FIELD AND MANAGE - so the answer for Job 100 would be 25.
I therefore need 2 formulas, one that just uses one criteris (OFFICE) and a second similar one that does the same but adds 2 dept types together.
The resulst being
Job Office Field + Managae
100 15 25
101 1 35
102 10 0
I've tried to achieve the same with a Pivot Table but the total for the 2 calculated hours columns doesn't work in some case due to this known issue.
Thanks in advance for any help
I have a sheet as follows (set up as a table - first row is headers):-
Job Dept Hours
100 Office 10
100 Office 5
100 Field 20
100 Manage 5
101 Office 1
101 Field 10
101 Field 20
101 Field 5
102 Office 10
On a separate sheet I use the UNIQUE function to create a unique dynamic list of column A (Job) from the first sheet. On this second sheet in col B (col A has the unique list) I want a formula to give me the total number of hours from col C on the first sheet for dept OFFICE - so in the case of Job 100 the answer would be 15. I'll then repeat the formula in col C but in this case I want to do the same BUT add the hours together for FIELD AND MANAGE - so the answer for Job 100 would be 25.
I therefore need 2 formulas, one that just uses one criteris (OFFICE) and a second similar one that does the same but adds 2 dept types together.
The resulst being
Job Office Field + Managae
100 15 25
101 1 35
102 10 0
I've tried to achieve the same with a Pivot Table but the total for the 2 calculated hours columns doesn't work in some case due to this known issue.
Thanks in advance for any help