Distinct Count based on two criteria (time involved)

[honestabe1982]

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!

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

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[jarjarbingie]

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!

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

<tbody>
</tbody>

<tbody>
</tbody>

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

 

[honestabe1982]

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?

 

[johnmpl]

Hi, honestabe1982!

Try:

Book1
	A	B	C	D	E	F	G	H	I	J
1	TIMESTAMP	ANI	CALL TIME	CAMPAIGN		Count -->	4		CAMPAIGN	DISTINCT COUNT
2	Mon, 28 Aug 2017 12:04:10	2014515824	0:08:40	Inbound - Double Verified 4		Greater than -->	0:00:29		Inbound - Double Verified 4	2
3	Mon, 28 Aug 2017 09:16:49	2022626840	0:00:10	Inbound - Live TV4					Inbound - Live TV4	13
4	Mon, 28 Aug 2017 12:32:29	2022765447	0:01:28	Inbound - Live TV4					Inbound - Internet Lead	2
5	Mon, 28 Aug 2017 19:05:51	2024760812	0:00:09	Inbound - Live TV4					Inbound - Live TV5	3
6	Tue, 29 Aug 2017 15:41:02	2025621775	0:05:13	Inbound - Internet Lead
7	Tue, 29 Aug 2017 13:06:08	2028295219	1:03:57	Inbound - Double Verified 4
8	Mon, 28 Aug 2017 12:32:37	2055524685	0:01:19	Inbound - Live TV4
9	Mon, 28 Aug 2017 11:33:15	2062940772	0:01:53	Inbound - Live TV4
10	Mon, 28 Aug 2017 12:33:16	2064878245	0:13:12	Inbound - Live TV4
11	Tue, 29 Aug 2017 11:52:23	2066641724	1:09:33	Inbound - Live TV4
12	Tue, 29 Aug 2017 17:47:53	2092022265	0:00:05	Inbound - Live TV5
13	Mon, 28 Aug 2017 16:38:45	2093126143	0:01:32	Inbound - Internet Lead
14	Tue, 29 Aug 2017 12:47:25	2093457470	0:16:44	Inbound - Live TV4
15	Tue, 29 Aug 2017 13:56:05	2095672847	1:44:50	Inbound - Live TV4
16	Tue, 29 Aug 2017 13:13:37	2097285517	0:02:59	Inbound - Live TV4
17	Tue, 29 Aug 2017 12:53:15	2098269164	0:15:18	Inbound - Live TV5
18	Tue, 29 Aug 2017 17:48:59	2098292391	0:00:37	Inbound - Live TV5
19	Tue, 29 Aug 2017 17:55:18	2098292391	0:00:22	Inbound - Live TV5
20	Mon, 28 Aug 2017 13:25:07	2098298832	0:04:54	Inbound - Live TV4
21	Tue, 29 Aug 2017 16:33:03	2103070396	1:36:38	Inbound - Live TV4
22	Mon, 28 Aug 2017 13:21:23	2103153053	1:22:14	Inbound - Live TV5
23	Mon, 28 Aug 2017 11:34:40	2103332978	1:18:00	Inbound - Live TV4
24	Tue, 29 Aug 2017 16:15:40	2103866212	0:01:17	Inbound - Live TV4
25	Mon, 28 Aug 2017 16:57:16	2104101834	0:02:55	Inbound - Live TV4
Hoja1

Cell Formulas
Range	Formula
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!

 

[honestabe1982]

Hi, honestabe1982!

Try:

	A	B	C	D	E	F	G	H	I	J
1	TIMESTAMP	ANI	CALL TIME	CAMPAIGN		Count -->	4		CAMPAIGN	DISTINCT COUNT
2	Mon, 28 Aug 2017 12:04:10	2014515824	0:08:40	Inbound - Double Verified 4		Greater than -->	0:00:29		Inbound - Double Verified 4	2
3	Mon, 28 Aug 2017 09:16:49	2022626840	0:00:10	Inbound - Live TV4					Inbound - Live TV4	13
4	Mon, 28 Aug 2017 12:32:29	2022765447	0:01:28	Inbound - Live TV4					Inbound - Internet Lead	2
5	Mon, 28 Aug 2017 19:05:51	2024760812	0:00:09	Inbound - Live TV4					Inbound - Live TV5	3
6	Tue, 29 Aug 2017 15:41:02	2025621775	0:05:13	Inbound - Internet Lead
7	Tue, 29 Aug 2017 13:06:08	2028295219	1:03:57	Inbound - Double Verified 4
8	Mon, 28 Aug 2017 12:32:37	2055524685	0:01:19	Inbound - Live TV4
9	Mon, 28 Aug 2017 11:33:15	2062940772	0:01:53	Inbound - Live TV4
10	Mon, 28 Aug 2017 12:33:16	2064878245	0:13:12	Inbound - Live TV4
11	Tue, 29 Aug 2017 11:52:23	2066641724	1:09:33	Inbound - Live TV4
12	Tue, 29 Aug 2017 17:47:53	2092022265	0:00:05	Inbound - Live TV5
13	Mon, 28 Aug 2017 16:38:45	2093126143	0:01:32	Inbound - Internet Lead
14	Tue, 29 Aug 2017 12:47:25	2093457470	0:16:44	Inbound - Live TV4
15	Tue, 29 Aug 2017 13:56:05	2095672847	1:44:50	Inbound - Live TV4
16	Tue, 29 Aug 2017 13:13:37	2097285517	0:02:59	Inbound - Live TV4
17	Tue, 29 Aug 2017 12:53:15	2098269164	0:15:18	Inbound - Live TV5
18	Tue, 29 Aug 2017 17:48:59	2098292391	0:00:37	Inbound - Live TV5
19	Tue, 29 Aug 2017 17:55:18	2098292391	0:00:22	Inbound - Live TV5
20	Mon, 28 Aug 2017 13:25:07	2098298832	0:04:54	Inbound - Live TV4
21	Tue, 29 Aug 2017 16:33:03	2103070396	1:36:38	Inbound - Live TV4
22	Mon, 28 Aug 2017 13:21:23	2103153053	1:22:14	Inbound - Live TV5
23	Mon, 28 Aug 2017 11:34:40	2103332978	1:18:00	Inbound - Live TV4
24	Tue, 29 Aug 2017 16:15:40	2103866212	0:01:17	Inbound - Live TV4
25	Mon, 28 Aug 2017 16:57:16	2104101834	0:02:55	Inbound - Live TV4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Hoja1​

Worksheet Formulas Cell Formula 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),),))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula 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)))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Blessings!

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

Blessings right back at cha!

 

[SpillerBD]

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.

 

[SpillerBD]

29 minutes = 00:29:00.0
29 seconds = 00:00:29.0