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
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
[/TD]
[TD]Start Time
[/TD]
[TD]End Time
[/TD]
[TD]State
[/TD]
[TD]Additional Info
[/TD]
[TD]Duration
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
[/TD]
[TD]
3/8/2018 11:04​
[/TD]
[TD]
3/8/2018 11:07​
[/TD]
[TD]INBOUND Receiver Connect
[/TD]
[TD]
69971099​
[/TD]
[TD]
159​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
[/TD]
[TD]
3/8/2018 11:07​
[/TD]
[TD]
3/8/2018 11:07​
[/TD]
[TD]INBOUND Receiver Wrap
[/TD]
[TD]
69971099​
[/TD]
[TD]
33​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
[/TD]
[TD]
3/8/2018 11:07​
[/TD]
[TD]
3/8/2018 11:07​
[/TD]
[TD]AfterCallWork
[/TD]
[TD]NO REASON
[/TD]
[TD]
33​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
[/TD]
[TD]
3/8/2018 11:07​
[/TD]
[TD]
3/8/2018 11:26​
[/TD]
[TD]NotReady
[/TD]
[TD]ReasonCode=SCHEDULE_BREAK_EN
[/TD]
[TD]
1142​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
[/TD]
[TD]
3/8/2018 11:07​
[/TD]
[TD]
3/8/2018 11:26​
[/TD]
[TD]NotReady
[/TD]
[TD]ReasonCode=UNSCHEDULE_BREAK_EN
[/TD]
[TD]
1142​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
[/TD]
[TD]
3/8/2018 11:26​
[/TD]
[TD]
3/8/2018 11:32​
[/TD]
[TD]Ready
[/TD]
[TD]NO REASON
[/TD]
[TD]
362​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​
[/TD]
[TD]
3/8/2018 11:32​
[/TD]
[TD]
3/8/2018 11:33​
[/TD]
[TD]INBOUND Receiver Alert
[/TD]
[TD]
69980243​
[/TD]
[TD]
10​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​
[/TD]
[TD]
3/8/2018 11:32​
[/TD]
[TD]
3/8/2018 11:43​
[/TD]
[TD]Busy
[/TD]
[TD]NO REASON
[/TD]
[TD]
620​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​
[/TD]
[TD]
3/8/2018 11:33​
[/TD]
[TD]
3/8/2018 11:43​
[/TD]
[TD]INBOUND Receiver Connect
[/TD]
[TD]
69980243​
[/TD]
[TD]
610​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​
[/TD]
[TD]
3/8/2018 11:43​
[/TD]
[TD]
3/8/2018 11:45​
[/TD]
[TD]INBOUND Receiver Wrap
[/TD]
[TD]
69980243​
[/TD]
[TD]
120​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​
[/TD]
[TD]
3/8/2018 11:43​
[/TD]
[TD]
3/8/2018 11:45​
[/TD]
[TD]AfterCallWork
[/TD]
[TD]NO REASON
[/TD]
[TD]
120​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​
[/TD]
[TD]
3/8/2018 11:45​
[/TD]
[TD]
3/8/2018 11:49​
[/TD]
[TD]NotReady
[/TD]
[TD]ReasonCode=CALLBACK_EN
[/TD]
[TD]
437​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​
[/TD]
[TD]
3/8/2018 11:49​
[/TD]
[TD]
3/8/2018 11:56​
[/TD]
[TD]Ready
[/TD]
[TD]NO REASON
[/TD]
[TD]
416​
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Raw_Data
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
You must log in or register to reply here.

Similar threads

P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
934
paulkc
P
V
  • Question Question
Returning Randomly Selected Value from Table based on Criteria
Replies
4
Views
804
Viau1337
V
V
  • Question 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
1K
VikingLink
V
P
  • Solved
Splitting text into columns where text is located across multiple adjacent cells?
Replies
7
Views
1K
Peter_SSs
Peter_SSs
J
  • Question Question
Populate columns based on multiple criteria
Replies
0
Views
507
JonasTiger
J

Forum statistics

Threads
1,224,823
Messages
6,181,176
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