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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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
468
amolvijay
A
A
  • Solved
Count entry by time
Replies
3
Views
194
admvlad
A
K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
600
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
856
AquaBuoy
A
V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
499
etaf
etaf

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

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