Distinct Count based on two criteria (time involved)

honestabe1982

New Member
Joined
Aug 30, 2017
Messages
3
Please see the spreadsheet below. I've tried multiple array formulas with combination of sum/sumproduct/if/countif and no luck. I am trying to count the number of distinct ANI's that have a call time of greater than 0:00:29 and are of the campaign, Inbound - Live TV5.

I've been at this for hours using examples from other posts and no luck so far.

Please help.

Thanks!


[TABLE="width: 549"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]TIMESTAMP[/TD]
[TD]ANI[/TD]
[TD]CALL TIME[/TD]
[TD]CAMPAIGN[/TD]
[/TR]
[TR]
[TD]Mon, 28 Aug 2017 12:04:10[/TD]
[TD="align: right"]2014515824[/TD]
[TD="align: right"]0:08:40[/TD]
[TD]Inbound - Double Verified 4[/TD]
[/TR]
[TR]
[TD]Mon, 28 Aug 2017 09:16:49[/TD]
[TD="align: right"]2022626840[/TD]
[TD="align: right"]0:00:10[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
[TR]
[TD]Mon, 28 Aug 2017 12:32:29[/TD]
[TD="align: right"]2022765447[/TD]
[TD="align: right"]0:01:28[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
[TR]
[TD]Mon, 28 Aug 2017 19:05:51[/TD]
[TD="align: right"]2024760812[/TD]
[TD="align: right"]0:00:09[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
[TR]
[TD]Tue, 29 Aug 2017 15:41:02[/TD]
[TD="align: right"]2025621775[/TD]
[TD="align: right"]0:05:13[/TD]
[TD]Inbound - Internet Lead[/TD]
[/TR]
[TR]
[TD]Tue, 29 Aug 2017 13:06:08[/TD]
[TD="align: right"]2028295219[/TD]
[TD="align: right"]1:03:57[/TD]
[TD]Inbound - Double Verified 4[/TD]
[/TR]
[TR]
[TD]Mon, 28 Aug 2017 12:32:37[/TD]
[TD="align: right"]2055524685[/TD]
[TD="align: right"]0:01:19[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
[TR]
[TD]Mon, 28 Aug 2017 11:33:15[/TD]
[TD="align: right"]2062940772[/TD]
[TD="align: right"]0:01:53[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
[TR]
[TD]Mon, 28 Aug 2017 12:33:16[/TD]
[TD="align: right"]2064878245[/TD]
[TD="align: right"]0:13:12[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
[TR]
[TD]Tue, 29 Aug 2017 11:52:23[/TD]
[TD="align: right"]2066641724[/TD]
[TD="align: right"]1:09:33[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
[TR]
[TD]Tue, 29 Aug 2017 17:47:53[/TD]
[TD="align: right"]2092022265[/TD]
[TD="align: right"]0:00:05[/TD]
[TD]Inbound - Live TV5[/TD]
[/TR]
[TR]
[TD]Mon, 28 Aug 2017 16:38:45[/TD]
[TD="align: right"]2093126143[/TD]
[TD="align: right"]0:01:32[/TD]
[TD]Inbound - Internet Lead[/TD]
[/TR]
[TR]
[TD]Tue, 29 Aug 2017 12:47:25[/TD]
[TD="align: right"]2093457470[/TD]
[TD="align: right"]0:16:44[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
[TR]
[TD]Tue, 29 Aug 2017 13:56:05[/TD]
[TD="align: right"]2095672847[/TD]
[TD="align: right"]1:44:50[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
[TR]
[TD]Tue, 29 Aug 2017 13:13:37[/TD]
[TD="align: right"]2097285517[/TD]
[TD="align: right"]0:02:59[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
[TR]
[TD]Tue, 29 Aug 2017 12:53:15[/TD]
[TD="align: right"]2098269164[/TD]
[TD="align: right"]0:15:18[/TD]
[TD]Inbound - Live TV5[/TD]
[/TR]
[TR]
[TD]Tue, 29 Aug 2017 17:48:59[/TD]
[TD="align: right"]2098292391[/TD]
[TD="align: right"]0:00:37[/TD]
[TD]Inbound - Live TV5[/TD]
[/TR]
[TR]
[TD]Tue, 29 Aug 2017 17:55:18[/TD]
[TD="align: right"]2098292391[/TD]
[TD="align: right"]0:00:22[/TD]
[TD]Inbound - Live TV5[/TD]
[/TR]
[TR]
[TD]Mon, 28 Aug 2017 13:25:07[/TD]
[TD="align: right"]2098298832[/TD]
[TD="align: right"]0:04:54[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
[TR]
[TD]Tue, 29 Aug 2017 16:33:03[/TD]
[TD="align: right"]2103070396[/TD]
[TD="align: right"]1:36:38[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
[TR]
[TD]Mon, 28 Aug 2017 13:21:23[/TD]
[TD="align: right"]2103153053[/TD]
[TD="align: right"]1:22:14[/TD]
[TD]Inbound - Live TV5[/TD]
[/TR]
[TR]
[TD]Mon, 28 Aug 2017 11:34:40[/TD]
[TD="align: right"]2103332978[/TD]
[TD="align: right"]1:18:00[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
[TR]
[TD]Tue, 29 Aug 2017 16:15:40[/TD]
[TD="align: right"]2103866212[/TD]
[TD="align: right"]0:01:17[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
[TR]
[TD]Mon, 28 Aug 2017 16:57:16[/TD]
[TD="align: right"]2104101834[/TD]
[TD="align: right"]0:02:55[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 528"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Please see the spreadsheet below. I've tried multiple array formulas with combination of sum/sumproduct/if/countif and no luck. I am trying to count the number of distinct ANI's that have a call time of greater than 0:00:29 and are of the campaign, Inbound - Live TV5.

I've been at this for hours using examples from other posts and no luck so far.

Please help.

Thanks!


[TABLE="width: 549"]
<tbody>[TR]
[TD]TIMESTAMP[/TD]
[TD]ANI[/TD]
[TD]CALL TIME[/TD]
[TD]CAMPAIGN[/TD]
[/TR]
[TR]
[TD]Mon, 28 Aug 2017 12:04:10[/TD]
[TD="align: right"]2014515824[/TD]
[TD="align: right"]0:08:40[/TD]
[TD]Inbound - Double Verified 4[/TD]
[/TR]
[TR]
[TD]Mon, 28 Aug 2017 09:16:49[/TD]
[TD="align: right"]2022626840[/TD]
[TD="align: right"]0:00:10[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
[TR]
[TD]Mon, 28 Aug 2017 12:32:29[/TD]
[TD="align: right"]2022765447[/TD]
[TD="align: right"]0:01:28[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
[TR]
[TD]Mon, 28 Aug 2017 19:05:51[/TD]
[TD="align: right"]2024760812[/TD]
[TD="align: right"]0:00:09[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
[TR]
[TD]Tue, 29 Aug 2017 15:41:02[/TD]
[TD="align: right"]2025621775[/TD]
[TD="align: right"]0:05:13[/TD]
[TD]Inbound - Internet Lead[/TD]
[/TR]
[TR]
[TD]Tue, 29 Aug 2017 13:06:08[/TD]
[TD="align: right"]2028295219[/TD]
[TD="align: right"]1:03:57[/TD]
[TD]Inbound - Double Verified 4[/TD]
[/TR]
[TR]
[TD]Mon, 28 Aug 2017 12:32:37[/TD]
[TD="align: right"]2055524685[/TD]
[TD="align: right"]0:01:19[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
[TR]
[TD]Mon, 28 Aug 2017 11:33:15[/TD]
[TD="align: right"]2062940772[/TD]
[TD="align: right"]0:01:53[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
[TR]
[TD]Mon, 28 Aug 2017 12:33:16[/TD]
[TD="align: right"]2064878245[/TD]
[TD="align: right"]0:13:12[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
[TR]
[TD]Tue, 29 Aug 2017 11:52:23[/TD]
[TD="align: right"]2066641724[/TD]
[TD="align: right"]1:09:33[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
[TR]
[TD]Tue, 29 Aug 2017 17:47:53[/TD]
[TD="align: right"]2092022265[/TD]
[TD="align: right"]0:00:05[/TD]
[TD]Inbound - Live TV5[/TD]
[/TR]
[TR]
[TD]Mon, 28 Aug 2017 16:38:45[/TD]
[TD="align: right"]2093126143[/TD]
[TD="align: right"]0:01:32[/TD]
[TD]Inbound - Internet Lead[/TD]
[/TR]
[TR]
[TD]Tue, 29 Aug 2017 12:47:25[/TD]
[TD="align: right"]2093457470[/TD]
[TD="align: right"]0:16:44[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
[TR]
[TD]Tue, 29 Aug 2017 13:56:05[/TD]
[TD="align: right"]2095672847[/TD]
[TD="align: right"]1:44:50[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
[TR]
[TD]Tue, 29 Aug 2017 13:13:37[/TD]
[TD="align: right"]2097285517[/TD]
[TD="align: right"]0:02:59[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
[TR]
[TD]Tue, 29 Aug 2017 12:53:15[/TD]
[TD="align: right"]2098269164[/TD]
[TD="align: right"]0:15:18[/TD]
[TD]Inbound - Live TV5[/TD]
[/TR]
[TR]
[TD]Tue, 29 Aug 2017 17:48:59[/TD]
[TD="align: right"]2098292391[/TD]
[TD="align: right"]0:00:37[/TD]
[TD]Inbound - Live TV5[/TD]
[/TR]
[TR]
[TD]Tue, 29 Aug 2017 17:55:18[/TD]
[TD="align: right"]2098292391[/TD]
[TD="align: right"]0:00:22[/TD]
[TD]Inbound - Live TV5[/TD]
[/TR]
[TR]
[TD]Mon, 28 Aug 2017 13:25:07[/TD]
[TD="align: right"]2098298832[/TD]
[TD="align: right"]0:04:54[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
[TR]
[TD]Tue, 29 Aug 2017 16:33:03[/TD]
[TD="align: right"]2103070396[/TD]
[TD="align: right"]1:36:38[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
[TR]
[TD]Mon, 28 Aug 2017 13:21:23[/TD]
[TD="align: right"]2103153053[/TD]
[TD="align: right"]1:22:14[/TD]
[TD]Inbound - Live TV5[/TD]
[/TR]
[TR]
[TD]Mon, 28 Aug 2017 11:34:40[/TD]
[TD="align: right"]2103332978[/TD]
[TD="align: right"]1:18:00[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
[TR]
[TD]Tue, 29 Aug 2017 16:15:40[/TD]
[TD="align: right"]2103866212[/TD]
[TD="align: right"]0:01:17[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
[TR]
[TD]Mon, 28 Aug 2017 16:57:16[/TD]
[TD="align: right"]2104101834[/TD]
[TD="align: right"]0:02:55[/TD]
[TD]Inbound - Live TV4[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 528"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I couldn't do it without helper columns.. if you're okay with that, then see below:

E2=AND(C2>29/24/60/60,D2="Inbound - Live TV5")
F2=IF(E2,B2,"")

distinct count:
=SUM(IF(($E$2:$E$25)*(MATCH($F$2:$F$25,$F$2:$F$25,0)=ROW($F$2:$F$25)-1),1,0)) press CTRL+SHIFT+ENTER
 
Upvote 0
I couldn't do it without helper columns.. if you're okay with that, then see below:

E2=AND(C2>29/24/60/60,D2="Inbound - Live TV5")
F2=IF(E2,B2,"")

distinct count:
=SUM(IF(($E$2:$E$25)*(MATCH($F$2:$F$25,$F$2:$F$25,0)=ROW($F$2:$F$25)-1),1,0)) press CTRL+SHIFT+ENTER


First of all, thank you for taking the time to reply. While this looks like it would work for Live TV5, I would prefer a way to do it without if possible. The reason being is I need to do it for all the different campaigns, not just Live TV5. So, I'm looking for about 10 different cells to contain formulas that look to this sheet and check for greater than a certain amount of time (varies by campaign), look for the specific campaign and then kick back the distinct count of phone numbers according to those two criteria.

Seems like that would get a bit messy to do the proposed above for that many different variables.

Thoughts?
 
Upvote 0
Hi, honestabe1982!

Try:


Book1
ABCDEFGHIJ
1TIMESTAMPANICALL TIMECAMPAIGNCount -->4CAMPAIGNDISTINCT COUNT
2Mon, 28 Aug 2017 12:04:1020145158240:08:40Inbound - Double Verified 4Greater than -->0:00:29Inbound - Double Verified 42
3Mon, 28 Aug 2017 09:16:4920226268400:00:10Inbound - Live TV4Inbound - Live TV413
4Mon, 28 Aug 2017 12:32:2920227654470:01:28Inbound - Live TV4Inbound - Internet Lead2
5Mon, 28 Aug 2017 19:05:5120247608120:00:09Inbound - Live TV4Inbound - Live TV53
6Tue, 29 Aug 2017 15:41:0220256217750:05:13Inbound - Internet Lead
7Tue, 29 Aug 2017 13:06:0820282952191:03:57Inbound - Double Verified 4
8Mon, 28 Aug 2017 12:32:3720555246850:01:19Inbound - Live TV4
9Mon, 28 Aug 2017 11:33:1520629407720:01:53Inbound - Live TV4
10Mon, 28 Aug 2017 12:33:1620648782450:13:12Inbound - Live TV4
11Tue, 29 Aug 2017 11:52:2320666417241:09:33Inbound - Live TV4
12Tue, 29 Aug 2017 17:47:5320920222650:00:05Inbound - Live TV5
13Mon, 28 Aug 2017 16:38:4520931261430:01:32Inbound - Internet Lead
14Tue, 29 Aug 2017 12:47:2520934574700:16:44Inbound - Live TV4
15Tue, 29 Aug 2017 13:56:0520956728471:44:50Inbound - Live TV4
16Tue, 29 Aug 2017 13:13:3720972855170:02:59Inbound - Live TV4
17Tue, 29 Aug 2017 12:53:1520982691640:15:18Inbound - Live TV5
18Tue, 29 Aug 2017 17:48:5920982923910:00:37Inbound - Live TV5
19Tue, 29 Aug 2017 17:55:1820982923910:00:22Inbound - Live TV5
20Mon, 28 Aug 2017 13:25:0720982988320:04:54Inbound - Live TV4
21Tue, 29 Aug 2017 16:33:0321030703961:36:38Inbound - Live TV4
22Mon, 28 Aug 2017 13:21:2321031530531:22:14Inbound - Live TV5
23Mon, 28 Aug 2017 11:34:4021033329781:18:00Inbound - Live TV4
24Tue, 29 Aug 2017 16:15:4021038662120:01:17Inbound - Live TV4
25Mon, 28 Aug 2017 16:57:1621041018340:02:55Inbound - Live TV4
Hoja1
Cell Formulas
RangeFormula
G1=SUM(N(FREQUENCY(MATCH(D$2:D$25,D$2:D$25,),ROW(D$2:D$25)-ROW(D$1))>0))
I2=IF(ROWS(I$2:I2)>G$1,"",INDEX(D$2:D$25,MATCH(,INDEX(COUNTIF(I$1:I1,D$2:D$25),),)))
J2{=IF(I2="","",SUM(N(FREQUENCY(IF(C$2:C$25>G$2,IF(D$2:D$25=I2,MATCH(B$2:B$25,B$2:B$25,))),ROW(B$2:B$25)-ROW(B$1))>0)))}
Press CTRL+SHIFT+ENTER to enter array formulas.


Blessings!
 
Last edited:
Upvote 0
Hi, honestabe1982!

Try:

ABCDEFGHIJ
TIMESTAMPANICALL TIMECAMPAIGNCAMPAIGNDISTINCT COUNT
Mon, 28 Aug 2017 12:04:10Inbound - Double Verified 4Greater than -->
Mon, 28 Aug 2017 09:16:49Inbound - Live TV4
Mon, 28 Aug 2017 12:32:29Inbound - Live TV4
Mon, 28 Aug 2017 19:05:51Inbound - Live TV4
Tue, 29 Aug 2017 15:41:02Inbound - Internet Lead
Tue, 29 Aug 2017 13:06:08Inbound - Double Verified 4
Mon, 28 Aug 2017 12:32:37Inbound - Live TV4
Mon, 28 Aug 2017 11:33:15Inbound - Live TV4
Mon, 28 Aug 2017 12:33:16Inbound - Live TV4
Tue, 29 Aug 2017 11:52:23Inbound - Live TV4
Tue, 29 Aug 2017 17:47:53Inbound - Live TV5
Mon, 28 Aug 2017 16:38:45Inbound - Internet Lead
Tue, 29 Aug 2017 12:47:25Inbound - Live TV4
Tue, 29 Aug 2017 13:56:05Inbound - Live TV4
Tue, 29 Aug 2017 13:13:37Inbound - Live TV4
Tue, 29 Aug 2017 12:53:15Inbound - Live TV5
Tue, 29 Aug 2017 17:48:59Inbound - Live TV5
Tue, 29 Aug 2017 17:55:18Inbound - Live TV5
Mon, 28 Aug 2017 13:25:07Inbound - Live TV4
Tue, 29 Aug 2017 16:33:03Inbound - Live TV4
Mon, 28 Aug 2017 13:21:23Inbound - Live TV5
Mon, 28 Aug 2017 11:34:40Inbound - Live TV4
Tue, 29 Aug 2017 16:15:40Inbound - Live TV4
Mon, 28 Aug 2017 16:57:16Inbound - Live TV4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"]Count -->[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2014515824[/TD]
[TD="align: right"]0:08:40[/TD]

[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: center"]0:00:29[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]Inbound - Double Verified 4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2022626840[/TD]
[TD="align: right"]0:00:10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]Inbound - Live TV4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]13[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2022765447[/TD]
[TD="align: right"]0:01:28[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]Inbound - Internet Lead[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]2[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]2024760812[/TD]
[TD="align: right"]0:00:09[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "]Inbound - Live TV5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]3[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]2025621775[/TD]
[TD="align: right"]0:05:13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]2028295219[/TD]
[TD="align: right"]1:03:57[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]2055524685[/TD]
[TD="align: right"]0:01:19[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]2062940772[/TD]
[TD="align: right"]0:01:53[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]2064878245[/TD]
[TD="align: right"]0:13:12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] "][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]2066641724[/TD]
[TD="align: right"]1:09:33[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]2092022265[/TD]
[TD="align: right"]0:00:05[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]2093126143[/TD]
[TD="align: right"]0:01:32[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]2093457470[/TD]
[TD="align: right"]0:16:44[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]2095672847[/TD]
[TD="align: right"]1:44:50[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]2097285517[/TD]
[TD="align: right"]0:02:59[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]2098269164[/TD]
[TD="align: right"]0:15:18[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]2098292391[/TD]
[TD="align: right"]0:00:37[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]2098292391[/TD]
[TD="align: right"]0:00:22[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]2098298832[/TD]
[TD="align: right"]0:04:54[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]2103070396[/TD]
[TD="align: right"]1:36:38[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]2103153053[/TD]
[TD="align: right"]1:22:14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"]2103332978[/TD]
[TD="align: right"]1:18:00[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]

[TD="align: right"]2103866212[/TD]
[TD="align: right"]0:01:17[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]

[TD="align: right"]2104101834[/TD]
[TD="align: right"]0:02:55[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Hoja1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G1[/TH]
[TD="align: left"]=SUM(N(FREQUENCY(MATCH(D$2:D$25,D$2:D$25,),ROW(D$2:D$25)-ROW(D$1))>0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]=IF(ROWS(I$2:I2)>G$1,"",INDEX(D$2:D$25,MATCH(,INDEX(COUNTIF(I$1:I1,D$2:D$25),),)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J2[/TH]
[TD="align: left"]{=IF(I2="","",SUM(N(FREQUENCY(IF(C$2:C$25>G$2,IF(D$2:D$25=I2,MATCH(B$2:B$25,B$2:B$25,))),ROW(B$2:B$25)-ROW(B$1))>0)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Blessings!



This worked for me. Thank you for your help!!

Blessings right back at cha!
 
Upvote 0
This is a case where, while you can use Pivot Tables, the method must be specific. When using data from an OLAP source or from the Data Model, you have a Distinct Count option for Values.
For this data set, its actually applying the filter to Call Time that presents more issue. Excel likes filtering Dates (automatically) rather than time. Except for this anomaly, I think setting up the Pivot Table is easier.
 
Upvote 0

Forum statistics

Threads
1,224,454
Messages
6,178,764
Members
452,874
Latest member
dasbach

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