SUMPRODUCT on multiple ranges / criteria

drewberts

Board Regular
Joined
Sep 5, 2013
Messages
177
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
If your "Office" Header for the result is in G2, try:

Code:
G3 =SUMIFS($C:$C,$A:$A,$F3,$B:$B,LEFT(G$2,FIND("+",G$2&" +")-2))+SUMIFS($C:$C,$A:$A,$F3,$B:$B,REPLACE(G$2,1,FIND("+",G$2&" +")+1,""))

Then, copy G3 to G3:H5
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top