SUMIFS using multiple criteria and removing rows based on duplicates in another column

J

jhaiisiin

New Member
Joined
Mar 13, 2018
Messages
1
It's possibly my search-fu is failing me here, or more likely I simply don't understand the answers I've found. I'm working on a project (due to some backend db errors that will eventually be resolved) that requires me to tabulate the duration of certain phone states. Unfortunately, the aforementioned errors create duplicate durations that I need to remove from my sums.

The raw data is imported from another program via .csv, and the column lengths will vary depending on the when and on who the report is run, so I can't specify specific column lengths. The duration total will be tallied on a separate sheet in the workbook, and I'd prefer to use a formula rather than VBA in this case.

I've seen various solutions using SUMIFS with FREQUENCY to edit out duplicates. I truly haven't been able to understand the proper syntax on FREQUENCY to get it to work for me. I've seen various usages of IFs and MATCH and ROWs but I don't understand how they're fitting together, so I've been unable to adapt those to my work.

I've included sample data below. Here's what I'm trying to do:
SUM the Duration column values for all instances of NotReady in the State column, but exclude duplicate Start Time results from that list.
I need to use the Start Time column as my duplicate check source, since it's possible for 2 NotReady states to have the same duration and still be valid.

I'm able to easily SUM the duration for the NotReady states. That part was easy. I've tried using FREQUENCY but I'm absolutely certain I just don't know how to wield it properly. Here's an example of what my formula might look like (minus the FREQUENCY segment, obvously)

Code: 
=SUMIFS(Raw_Data!D:D,Raw_Data!C:C,"NotReady",Raw_Data!A:A,[Code to remove duplicates])
I'm aware the result will be CSE. I just need help figuring out some appropriate syntax.

If it were to run right on this snippet, it would only SUM the Duration values from row 5 or 6, and row 13, for a total of 1579. Any help you can grant to get me to understand will be greatly appreciated.

Data example

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
Start Time
End Time
State
Additional Info
Duration

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​

3/8/2018 11:04​

3/8/2018 11:07​
INBOUND Receiver Connect

69971099​

159​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​

3/8/2018 11:07​

3/8/2018 11:07​
INBOUND Receiver Wrap

69971099​

33​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​

3/8/2018 11:07​

3/8/2018 11:07​
AfterCallWork
NO REASON

33​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​

3/8/2018 11:07​

3/8/2018 11:26​
NotReady
ReasonCode=SCHEDULE_BREAK_EN

1142​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​

3/8/2018 11:07​

3/8/2018 11:26​
NotReady
ReasonCode=UNSCHEDULE_BREAK_EN

1142​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​

3/8/2018 11:26​

3/8/2018 11:32​
Ready
NO REASON

362​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​

3/8/2018 11:32​

3/8/2018 11:33​
INBOUND Receiver Alert

69980243​

10​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​

3/8/2018 11:32​

3/8/2018 11:43​
Busy
NO REASON

620​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​

3/8/2018 11:33​

3/8/2018 11:43​
INBOUND Receiver Connect

69980243​

610​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​

3/8/2018 11:43​

3/8/2018 11:45​
INBOUND Receiver Wrap

69980243​

120​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​

3/8/2018 11:43​

3/8/2018 11:45​
AfterCallWork
NO REASON

120​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​

3/8/2018 11:45​

3/8/2018 11:49​
NotReady
ReasonCode=CALLBACK_EN

437​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​

3/8/2018 11:49​

3/8/2018 11:56​
Ready
NO REASON

416​

<tbody>
</tbody>
Sheet: Raw_Data

<tbody>
</tbody>
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
You must log in or register to reply here.

Similar threads

V
  • Question
Returning Randomly Selected Value from Table based on Criteria
Replies
4
Views
587
Viau1337
V
V
  • Question
VBA to find an copy rows based on contents from another cell/remove unwanted numbers and trailing spaces in multiple rows. And some easier things.
Replies
1
Views
819
VikingLink
V
P
  • Solved
Splitting text into columns where text is located across multiple adjacent cells?
Replies
7
Views
805
Peter_SSs
Peter_SSs
J
  • Question
Populate columns based on multiple criteria
Replies
0
Views
400
JonasTiger
J
W
  • Solved
How to total unique values in one column between dates in another column?
Replies
1
Views
542
AhoyNC
A

Forum statistics

Threads
1,214,647
Messages
6,120,722
Members
448,987
Latest member
marion_davis

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