Sumifs Formula Help 2 Criteria i think

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
2,125
Office Version
  1. 365
Platform
  1. Windows
Looking to get a total sum if 2 Criteria’s match.

Look at Row number 1

I need to match ID number 5 in Column D and also the job name called 153 5th if located in H,L,P,T,X,AA or AD. Then giving me total sums of the numbers in column E,I,M,Q,U,Y,AB.

You can see in Row 1. ID# 5 is in column D and the job name 153 5th matches also which would return the total of 16 because 8 and 8 are in column E and I and the job name matchs in H and L 153 5th.

Result in AF would be 16.

Hope I explained it right.

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]
A
[/th][th]
B
[/th][th]
C
[/th][th]
D
[/th][th]
E
[/th][th]
F
[/th][th]
G
[/th][th]
H
[/th][th]
I
[/th][th]
J
[/th][th]
K
[/th][th]
L
[/th][th]
M
[/th][th]
N
[/th][th]
O
[/th][th]
P
[/th][th]
Q
[/th][th]
R
[/th][th]
S
[/th][th]
T
[/th][th]
U
[/th][th]
V
[/th][th]
W
[/th][th]
X
[/th][th]
Y
[/th][th]
Z
[/th][th]
AA
[/th][th]
AB
[/th][th]
AC
[/th][th]
AD
[/th][th]
AE
[/th][th]
AF
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
1
[/td][td]
A
[/td][td]
B
[/td][td]
C
[/td][td]
D
[/td][td]
E
[/td][td]
F
[/td][td]
G
[/td][td]
H
[/td][td]
I
[/td][td]
J
[/td][td]
K
[/td][td]
L
[/td][td]
M
[/td][td]
N
[/td][td]
O
[/td][td]
P
[/td][td]
Q
[/td][td]
R
[/td][td]
S
[/td][td]
T
[/td][td]
U
[/td][td]
V
[/td][td]
W
[/td][td]
X
[/td][td]
Y
[/td][td]
Z
[/td][td]
AA
[/td][td]
AB
[/td][td]
AC
[/td][td]
AD
[/td][td][/td][td]
AF
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
2
[/td][td]
1
[/td][td=bgcolor:#FFFFFF]Jose[/td][td=bgcolor:#FFFFFF]Labor[/td][td=bgcolor:#FFFFFF]
5
[/td][td=bgcolor:#FFFFFF]
8.00
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFF00]
153 5th
[/td][td=bgcolor:#FFFFFF]
8.00
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFF00]
153 5th
[/td][td=bgcolor:#FFFFFF]
8.00
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
225 5th
[/td][td=bgcolor:#FFFFFF]
8.00
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
100 1st
[/td][td=bgcolor:#FFFFFF]
8.00
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
100 1st
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
6.00
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
100 1st
[/td][td][/td][td]
16
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
3
[/td][td]
2
[/td][td=bgcolor:#FFFFFF]Jose[/td][td=bgcolor:#FFFFFF]Labor[/td][td=bgcolor:#FFFFFF]
7
[/td][td=bgcolor:#FFFFFF]
8.00
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
153 5th
[/td][td=bgcolor:#FFFFFF]
8.00
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
153 5th
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
8.00
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
100 1st
[/td][td=bgcolor:#FFFFFF]
8.00
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
100 1st
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td][/td][td]
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
4
[/td][td]
3
[/td][td=bgcolor:#FFFFFF]Jose[/td][td=bgcolor:#FFFFFF]Labor[/td][td=bgcolor:#FFFFFF]
8
[/td][td=bgcolor:#FFFFFF]
8.00
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
153 5th
[/td][td=bgcolor:#FFFFFF]
8.00
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
153 5th
[/td][td=bgcolor:#FFFFFF]
8.00
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
225 5th
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
5.00
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
100 1st
[/td][td=bgcolor:#FFFFFF]
6.00
[/td][td=bgcolor:#FFFFFF]
[/td][td=bgcolor:#FFFFFF]
100 1st
[/td][td][/td][td]
[/td][/tr]
[/table]
[Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
=SUMIFS(E2:E11,D2:D11,”5”,H2:H11,”153 5th”) This works but i think i have to keep adding on to this to go accross +
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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