count unique values if range meets criteria

[alirulez]

Hello

Hope you can help me out of this conundrum

I have a list constructed as follows

[TABLE="width: 500"]
<tbody>[TR]
[TD]User ID
[/TD]
[TD]Type[/TD]
[TD]Phone Numbers
[/TD]
[/TR]
[TR]
[TD]2231
[/TD]
[TD]Incoming
[/TD]
[TD]01159796555
[/TD]
[/TR]
[TR]
[TD]2231
[/TD]
[TD]Outgoing
[/TD]
[TD]01159796553
[/TD]
[/TR]
[TR]
[TD]2296
[/TD]
[TD]Local
[/TD]
[TD]01159795983
[/TD]
[/TR]
[TR]
[TD]2231
[/TD]
[TD]Toll
[/TD]
[TD]01159796523
[/TD]
[/TR]
[TR]
[TD]3400
[/TD]
[TD]Outgoing
[/TD]
[TD]01159796123
[/TD]
[/TR]
</tbody>[/TABLE]

Id like a formula which returns the number of unique Phone numbers, if the User ID is 2231, and If the Type is Not Incoming.

Ive tried a number of options with SumProduct and Count Ifs etc, but all have ended in failure

Best
Ali

 

[sandy666]

maybe PivotTable instead of formula?

 

[MARZIOTULLIO]

wrong formula

 

[AhoyNC]

Here are some formula ways.
Both are array formulas that must be entered with CTRL-SHIFT-ENTER.

Formula in B11 can only be used if phone numbers are numeric. Formula in B10 will work with either numeric are teat values.
Both of these formulas assume there will be no blanks in the phone number column. If there could be blanks we will need to add another IF statement in the formula.

Excel Workbook
	A	B	C
1	User ID	Type	Phone Numbers
2	2231	Incoming	1159796555
3	2231	Outgoing	1159796553
4	2296	Local	1159795983
5	2231	Toll	1159796523
6	3400	Outgoing	1159796123
7	2231	Outgoing	1159796553
8
9	User ID	2231
10	Count	2	If Phone # is text
11		2	If Phone # is numeric
Sheet

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]User ID[/TD]
[TD]Type[/TD]
[TD]Phone Numbers[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

2231​

[/TD]
[TD]Incoming[/TD]
[TD]

1159796555​

[/TD]
[TD][/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

2231​

[/TD]
[TD]Outgoing[/TD]
[TD]

1159796553​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

2296​

[/TD]
[TD]Local[/TD]
[TD]

1159795983​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

2231​

[/TD]
[TD]Toll[/TD]
[TD]

1159796523​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

3400​

[/TD]
[TD]Outgoing[/TD]
[TD]

1159796123​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


E2=SUM(IF(FREQUENCY(IF(A2:A6=A2,IF($B$2:$B$6<>B2,C2:C6)),C2:C6),1)) control+shift+enter