Distinct Count based on two criteria (time involved)

H

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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
honestabe1982 said:
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]
Click to expand...

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
jarjarbingie said:
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
Click to expand...


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
johnmpl said:
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!
Click to expand...



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
You must log in or register to reply here.

Similar threads

A
  • Question Question
Assign Month number 1 to n based on sequential week range
Replies
6
Views
486
amolvijay
A
Sandeep Warrier
  • Question Question
Calculate No. of Working Days based on Shift Pattern
Replies
2
Views
150
Tetra201
Tetra201
A
  • Solved
Count entry by time
Replies
3
Views
253
admvlad
A
K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
607
awoohaw
awoohaw
A
  • Question Question
Add a specified number of working hours to a date & time, given working day start, end and break times and holidays
Replies
2
Views
925
AquaBuoy
A

Forum statistics

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