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


AssetPM01
 PM02		PM03DamageOPTNEvents Count
(PM01, PM2)		Breakdown Events Count (PM03 - Damage)
UL04214
UL062
UL064
UL04263
UL066
UL4268
<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> </tbody>


Asset NumberStart down DateStart down timeFinish Down dateFinish Down timeCombine startCombine finishTotal Down timeType
UL04214
12-Jan-197:00 AM12-Jan-1912:00 PM12/01/2019 7:0012/01/2019 12:005.00PM01 - Preventive Maintenance
DJ06317/01/20194:45 AM17-Jan-197:00 AM17/01/2019 4:4517/01/2019 7:002.25PM03 - Breakdown
UL04214
18/01/20199:00 AM18-Jan-192:00 PM18/01/2019 9:0018/01/2019 14:005.00DA-Damage
TC440919/01/201910:30 AM19-Jan-195:00 PM19/01/2019 10:3019/01/2019 17:006.50PM03 - Breakdown
DJ411021/01/20195:30 AM22-Jan-195:00 PM21/01/2019 5:3022/01/2019 17:0035.50PM02 - Scheduled
TC440921/01/20198:25 AM21-Jan-1912:00 PM21/01/2019 8:2521/01/2019 12:003.58OPTN-Opportune work
LD0441822/01/20196:40 AM22-Jan-193:00 PM22/01/2019 6:4022/01/2019 15:008.33PM01 - Preventive Maintenance
TC440923/01/201911:00 AM24-Jan-192:00 PM23/01/2019 11:0024/01/2019 14:0027.00PM02 - Scheduled
DJ06324/01/20196:00 AM27-Jan-196:00 PM24/01/2019 6:0027/01/2019 18:0084.00PM02 - Scheduled
UL04214 0/01/1900 0:000/01/1900 0:000.00PM03 - Breakdown
UL06431/01/20196:00 AM31-Jan-1912:00 PM31/01/2019 6:0031/01/2019 12:006.00PM03 - Breakdown
UL04214
1/02/20198:00 AM1-Feb-199:00 AM1/02/2019 8:001/02/2019 9:001.00PM01 - Preventive Maintenance
DT43091/02/20197:00 AM1-Feb-199:30 AM1/02/2019 7:001/02/2019 9:302.50PM03 - Breakdown
GR044111/02/20196:00 AM1-Feb-199:00 AM1/02/2019 6:001/02/2019 9:003.00PM01 - Preventive Maintenance
TC441021/01/20196:00 AM4-Feb-196:00 AM21/01/2019 6:004/02/2019 6:00336.00PM03 - Breakdown
DJ4110 0.00
<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> </tbody>
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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

V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
212
etaf
etaf
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
324
awoohaw
awoohaw
M
  • Solved
Returning results based on multiple criteria
Replies
5
Views
292
DanteAmor
DanteAmor
L
  • Question
Formula Help with Count Number of Vessel Turnaround Trips monthly based on Criteria
Replies
1
Views
249
Lukma
L
K
  • Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
175
XLfriend777
XLfriend777

Forum statistics

Threads
1,215,432
Messages
6,124,860
Members
449,194
Latest member
HellScout

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