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

User ID	Type	Phone Numbers
2231	Incoming	01159796555
2231	Outgoing	01159796553
2296	Local	01159795983
2231	Toll	01159796523
3400	Outgoing	01159796123

<tbody>
</tbody>

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]

	A​	B​	C​	D​	E​
1​	User ID	Type	Phone Numbers
2​	2231​	Incoming	1159796555​		2​
3​	2231​	Outgoing	1159796553​
4​	2296​	Local	1159795983​
5​	2231​	Toll	1159796523​
6​	3400​	Outgoing	1159796123​

<tbody>
</tbody>

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