Sum If, multiple criteria

R

Ryan_sirait

New Member
Joined
Feb 5, 2019
Messages
1
Please help,

how can I sum all the downtime of UL4214 - from the data... tried usin Sumifs but still confusing


[TABLE="width: 392"]
<colgroup><col width="72" style="width: 54pt;" span="2"> <col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 2880;"> <col width="72" style="width: 54pt;" span="4"> <tbody>[TR]
[TD="width: 72, bgcolor: transparent"]Asset[/TD]
[TD="width: 72, bgcolor: transparent"]PM01
 PM02[/TD]
[TD="width: 90, bgcolor: transparent"]PM03[/TD]
[TD="width: 72, bgcolor: transparent"]Damage[/TD]
[TD="width: 72, bgcolor: transparent"]OPTN[/TD]
[TD="width: 72, bgcolor: transparent"]Events Count
(PM01, PM2)[/TD]
[TD="width: 72, bgcolor: transparent"]Breakdown Events Count (PM03 - Damage)[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]UL04214[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]UL062[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]UL064[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]UL04263[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]UL066[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]UL4268[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 634"]
<colgroup><col width="72" style="width: 54pt;"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2528;"> <col width="72" style="width: 54pt;" span="3"> <col width="120" style="width: 90pt; mso-width-source: userset; mso-width-alt: 3840;" span="2"> <col width="72" style="width: 54pt;"> <col width="167" style="width: 125pt; mso-width-source: userset; mso-width-alt: 5344;"> <tbody>[TR]
[TD="width: 72, bgcolor: #00B0F0"]Asset Number[/TD]
[TD="width: 79, bgcolor: #00B0F0"]Start down Date[/TD]
[TD="width: 72, bgcolor: #00B0F0"]Start down time[/TD]
[TD="width: 72, bgcolor: #00B0F0"]Finish Down date[/TD]
[TD="width: 72, bgcolor: #00B0F0"]Finish Down time[/TD]
[TD="width: 120, bgcolor: #00B0F0"]Combine start[/TD]
[TD="width: 120, bgcolor: #00B0F0"]Combine finish[/TD]
[TD="width: 72, bgcolor: #00B0F0"]Total Down time[/TD]
[TD="width: 167, bgcolor: #00B0F0"]Type[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]UL04214
[/TD]
[TD="bgcolor: transparent"]12-Jan-19[/TD]
[TD="bgcolor: transparent"]7:00 AM[/TD]
[TD="bgcolor: transparent"]12-Jan-19[/TD]
[TD="bgcolor: transparent"]12:00 PM[/TD]
[TD="bgcolor: transparent"]12/01/2019 7:00[/TD]
[TD="bgcolor: transparent"]12/01/2019 12:00[/TD]
[TD="bgcolor: white"]5.00[/TD]
[TD="width: 167, bgcolor: transparent"]PM01 - Preventive Maintenance[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DJ063[/TD]
[TD="bgcolor: transparent"]17/01/2019[/TD]
[TD="bgcolor: transparent"]4:45 AM[/TD]
[TD="bgcolor: transparent"]17-Jan-19[/TD]
[TD="bgcolor: transparent"]7:00 AM[/TD]
[TD="bgcolor: transparent"]17/01/2019 4:45[/TD]
[TD="bgcolor: transparent"]17/01/2019 7:00[/TD]
[TD="bgcolor: white"]2.25[/TD]
[TD="width: 167, bgcolor: transparent"]PM03 - Breakdown[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]UL04214
[/TD]
[TD="bgcolor: transparent"]18/01/2019[/TD]
[TD="bgcolor: transparent"]9:00 AM[/TD]
[TD="bgcolor: transparent"]18-Jan-19[/TD]
[TD="bgcolor: transparent"]2:00 PM[/TD]
[TD="bgcolor: transparent"]18/01/2019 9:00[/TD]
[TD="bgcolor: transparent"]18/01/2019 14:00[/TD]
[TD="bgcolor: white"]5.00[/TD]
[TD="width: 167, bgcolor: transparent"]DA-Damage[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TC4409[/TD]
[TD="bgcolor: transparent"]19/01/2019[/TD]
[TD="bgcolor: transparent"]10:30 AM[/TD]
[TD="bgcolor: transparent"]19-Jan-19[/TD]
[TD="bgcolor: transparent"]5:00 PM[/TD]
[TD="bgcolor: transparent"]19/01/2019 10:30[/TD]
[TD="bgcolor: transparent"]19/01/2019 17:00[/TD]
[TD="bgcolor: white"]6.50[/TD]
[TD="width: 167, bgcolor: transparent"]PM03 - Breakdown[/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"]DJ4110[/TD]
[TD="bgcolor: #92D050"]21/01/2019[/TD]
[TD="bgcolor: #92D050"]5:30 AM[/TD]
[TD="bgcolor: #92D050"]22-Jan-19[/TD]
[TD="bgcolor: #92D050"]5:00 PM[/TD]
[TD="bgcolor: #92D050"]21/01/2019 5:30[/TD]
[TD="bgcolor: #92D050"]22/01/2019 17:00[/TD]
[TD="bgcolor: #92D050"]35.50[/TD]
[TD="width: 167, bgcolor: #92D050"]PM02 - Scheduled[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TC4409[/TD]
[TD="bgcolor: transparent"]21/01/2019[/TD]
[TD="bgcolor: transparent"]8:25 AM[/TD]
[TD="bgcolor: transparent"]21-Jan-19[/TD]
[TD="bgcolor: transparent"]12:00 PM[/TD]
[TD="bgcolor: transparent"]21/01/2019 8:25[/TD]
[TD="bgcolor: transparent"]21/01/2019 12:00[/TD]
[TD="bgcolor: white"]3.58[/TD]
[TD="width: 167, bgcolor: transparent"]OPTN-Opportune work[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]LD04418[/TD]
[TD="bgcolor: transparent"]22/01/2019[/TD]
[TD="bgcolor: transparent"]6:40 AM[/TD]
[TD="bgcolor: transparent"]22-Jan-19[/TD]
[TD="bgcolor: transparent"]3:00 PM[/TD]
[TD="bgcolor: transparent"]22/01/2019 6:40[/TD]
[TD="bgcolor: transparent"]22/01/2019 15:00[/TD]
[TD="bgcolor: white"]8.33[/TD]
[TD="width: 167, bgcolor: transparent"]PM01 - Preventive Maintenance[/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"]TC4409[/TD]
[TD="bgcolor: #92D050"]23/01/2019[/TD]
[TD="bgcolor: #92D050"]11:00 AM[/TD]
[TD="bgcolor: #92D050"]24-Jan-19[/TD]
[TD="bgcolor: #92D050"]2:00 PM[/TD]
[TD="bgcolor: #92D050"]23/01/2019 11:00[/TD]
[TD="bgcolor: #92D050"]24/01/2019 14:00[/TD]
[TD="bgcolor: #92D050"]27.00[/TD]
[TD="width: 167, bgcolor: #92D050"]PM02 - Scheduled[/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"]DJ063[/TD]
[TD="bgcolor: #92D050"]24/01/2019[/TD]
[TD="bgcolor: #92D050"]6:00 AM[/TD]
[TD="bgcolor: #92D050"]27-Jan-19[/TD]
[TD="bgcolor: #92D050"]6:00 PM[/TD]
[TD="bgcolor: #92D050"]24/01/2019 6:00[/TD]
[TD="bgcolor: #92D050"]27/01/2019 18:00[/TD]
[TD="bgcolor: #92D050"]84.00[/TD]
[TD="width: 167, bgcolor: #92D050"]PM02 - Scheduled[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]UL04214[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]0/01/1900 0:00[/TD]
[TD="bgcolor: transparent"]0/01/1900 0:00[/TD]
[TD="bgcolor: white"]0.00[/TD]
[TD="width: 167, bgcolor: transparent"]PM03 - Breakdown[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]UL064[/TD]
[TD="bgcolor: transparent"]31/01/2019[/TD]
[TD="bgcolor: transparent"]6:00 AM[/TD]
[TD="bgcolor: transparent"]31-Jan-19[/TD]
[TD="bgcolor: transparent"]12:00 PM[/TD]
[TD="bgcolor: transparent"]31/01/2019 6:00[/TD]
[TD="bgcolor: transparent"]31/01/2019 12:00[/TD]
[TD="bgcolor: white"]6.00[/TD]
[TD="width: 167, bgcolor: transparent"]PM03 - Breakdown[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]UL04214
[/TD]
[TD="bgcolor: transparent"]1/02/2019[/TD]
[TD="bgcolor: transparent"]8:00 AM[/TD]
[TD="bgcolor: transparent"]1-Feb-19[/TD]
[TD="bgcolor: transparent"]9:00 AM[/TD]
[TD="bgcolor: transparent"]1/02/2019 8:00[/TD]
[TD="bgcolor: transparent"]1/02/2019 9:00[/TD]
[TD="bgcolor: white"]1.00[/TD]
[TD="width: 167, bgcolor: transparent"]PM01 - Preventive Maintenance[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DT4309[/TD]
[TD="bgcolor: transparent"]1/02/2019[/TD]
[TD="bgcolor: transparent"]7:00 AM[/TD]
[TD="bgcolor: transparent"]1-Feb-19[/TD]
[TD="bgcolor: transparent"]9:30 AM[/TD]
[TD="bgcolor: transparent"]1/02/2019 7:00[/TD]
[TD="bgcolor: transparent"]1/02/2019 9:30[/TD]
[TD="bgcolor: white"]2.50[/TD]
[TD="width: 167, bgcolor: transparent"]PM03 - Breakdown[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]GR04411[/TD]
[TD="bgcolor: transparent"]1/02/2019[/TD]
[TD="bgcolor: transparent"]6:00 AM[/TD]
[TD="bgcolor: transparent"]1-Feb-19[/TD]
[TD="bgcolor: transparent"]9:00 AM[/TD]
[TD="bgcolor: transparent"]1/02/2019 6:00[/TD]
[TD="bgcolor: transparent"]1/02/2019 9:00[/TD]
[TD="bgcolor: white"]3.00[/TD]
[TD="width: 167, bgcolor: transparent"]PM01 - Preventive Maintenance[/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"]TC4410[/TD]
[TD="bgcolor: #92D050"]21/01/2019[/TD]
[TD="bgcolor: #92D050"]6:00 AM[/TD]
[TD="bgcolor: #92D050"]4-Feb-19[/TD]
[TD="bgcolor: #92D050"]6:00 AM[/TD]
[TD="bgcolor: #92D050"]21/01/2019 6:00[/TD]
[TD="bgcolor: #92D050"]4/02/2019 6:00[/TD]
[TD="bgcolor: #92D050"]336.00[/TD]
[TD="width: 167, bgcolor: #92D050"]PM03 - Breakdown[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DJ4110[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]0.00[/TD]
[TD="width: 167, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Hi


There's no need for SumIFS as you only have one criteria better to use SumIF. e.g. =SUMIF($A$15:$A$30,A5,$H$15:$H$30)
Breakdown of SumIF

First part $A$15:$A$30, is the range which contains the criteria you want to sum by i.e. it contains a few or more UL04214.
Second part, A5 is the criteria you want to filter by UL04214 ie.
Third part $H$15:$H$30 is the actual sum range i.e. "Total Down time".

NB: the syntax is different to SUMIFS which can be confusing.

Hope this helps.
 
Upvote 0
And this would be using SUMIFS:
Excel Workbook
ABCDEFGHI
1UL0421411
2UL0620
3UL0646
4UL042630
5UL0660
6UL42680
7
8
9Asset NumberStart down DateStart down timeFinish Down dateFinish Down timeCombine startCombine finishTotal Down timeType
10UL0421412-Jan-197:00 AM12-Jan-1912:00 PM12/1/2019 7:0012/1/2019 12:005PM01 - Preventive Maintenance
11DJ06317/01/20194:45 AM17-Jan-197:00 AM17/01/2019 4:4517/01/2019 7:002.25PM03 - Breakdown
12UL0421418/01/20199:00 AM18-Jan-192:00 PM18/01/2019 9:0018/01/2019 14:005DA-Damage
13TC440919/01/201910:30 AM19-Jan-195:00 PM19/01/2019 10:3019/01/2019 17:006.5PM03 - Breakdown
14DJ411021/01/20195:30 AM22-Jan-195:00 PM21/01/2019 5:3022/01/2019 17:0035.5PM02 - Scheduled
15TC440921/01/20198:25 AM21-Jan-1912:00 PM21/01/2019 8:2521/01/2019 12:003.58OPTN-Opportune work
16LD0441822/01/20196:40 AM22-Jan-193:00 PM22/01/2019 6:4022/01/2019 15:008.33PM01 - Preventive Maintenance
17TC440923/01/201911:00 AM24-Jan-192:00 PM23/01/2019 11:0024/01/2019 14:0027PM02 - Scheduled
18DJ06324/01/20196:00 AM27-Jan-196:00 PM24/01/2019 6:0027/01/2019 18:0084PM02 - Scheduled
19UL042140/01/1900 0:000/01/1900 0:000PM03 - Breakdown
20UL06431/01/20196:00 AM31-Jan-1912:00 PM31/01/2019 6:0031/01/2019 12:006PM03 - Breakdown
21UL042141/2/20198:00 AM1-Feb-199:00 AM1/2/2019 8:001/2/2019 9:001PM01 - Preventive Maintenance
22DT43091/2/20197:00 AM1-Feb-199:30 AM1/2/2019 7:001/2/2019 9:302.5PM03 - Breakdown
23GR044111/2/20196:00 AM1-Feb-199:00 AM1/2/2019 6:001/2/2019 9:003PM01 - Preventive Maintenance
24TC441021/01/20196:00 AM4-Feb-196:00 AM21/01/2019 6:004/2/2019 6:00336PM03 - Breakdown
25DJ41100
Sheet
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Finding Numbers from a Column Using Target Numbers
2
Replies
11
Views
431
Cubist
Cubist
V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
506
etaf
etaf
J
  • Solved
Sorting a Lot of data into multiple sheets (up to 200k lines)
Replies
4
Views
343
JMWh1t3
J
welshgasman
  • Question Question
Formulae jumping references?
Replies
7
Views
101
welshgasman
welshgasman
tezza
  • Solved
Autosum specific rows and columns
Replies
2
Views
274
tezza
tezza

Forum statistics

Threads
1,224,820
Messages
6,181,157
Members
453,021
Latest member
Justyna P

Share this page

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