sum values in multiple columns based on one criteria

theYaniac

Board Regular
Joined
Jan 7, 2018
Messages
64
Office Version
  1. 365
Platform
  1. Windows
I am trying to sum values in multiple columns based on a single criteria. For example I have a summary table that is looking at 4 columns for straight time hours and 4 columns for overtime hours. I would like to sum the values in the columns based on the position criteria. I have tried to use a sumifs formula to achieve but keep returning an error. The sumifs formula would need to sum values in columns D:K based on the criteria in column B. Column B would describe position(ex. Apprentice, Journeyman, Superintendent, etc).

CD PositionCD Job NumberCD ST Hours Job 1CD ST Hours Job 2CD ST Hours Job 3CD ST Hours Job 4CD OT Hours Job 1CD OT Hours Job 2CD OT Hours Job 3CD OT Hours Job 4
Superintendent207824.004.00 0.003.50
Journeyman207824.004.00 0.003.50
Journeyman207824.004.00 0.003.50
Apprentice Level 7 & 8207824.004.00 0.003.50

<colgroup><col><col><col span="8"></colgroup><tbody>
</tbody>
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
something like...

=SUMPRODUCT((B2:B5="Journeyman")*(D2:G5)) which would sum the ST hours,

=SUMPRODUCT((B2:B5="Journeyman")*(H2:K5)) which would sum the OT hours
 
Upvote 0
the columns are going to grow daily. Will the sumproduct sum the values dynamically?
 
Upvote 0
Sheet1 (data + extension)

Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
K​
L​
1​
CD PositionCD Job NumberCD STCD OTCD ST Hours Job 1CD ST Hours Job 2CD ST Hours Job 3CD ST Hours Job 4CD OT Hours Job 1CD OT Hours Job 2CD OT Hours Job 3CD OT Hours Job 4
2​
Superintendent
20782
8
3.5
4
4
0
3.5
3​
Journeyman
20782
8
3.5
4
4
0
3.5
4​
Journeyman
20782
8
3.5
4
4
0
3.5
5​
Apprentice Level 7 & 8
20782
8
3.5
4
4
0
3.5

In C2 enter, copy across to D2, and down:

=SUMIFS(INDEX($E:$XFD,ROWS(C$1:C2),0),INDEX($E:$XFD,1,0),C$1&"*")

Row\Col
A​
B​
C​
D​
1​
CD STCD OT
2​
journeyman
16​
7​
3​

In B2 enter, copy across to C2, and down:

=SUMIFS(Sheet1!C:C,Sheet1!$A:$A,$A2)

Note that all totaling is done with SUMIFS such that adding new columns triggers immediate automatic update of all totals.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
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