Unique Values Formula with Multiple Criteria

[KARPRI11]

Hello
Please help me with a formula that will count unique values in one column if the values of 3 other columns match specific values. I believe I need a CSE formula. I want to count the unique values of column K which are tool IDs if column a,b,c are equal to specific values. Similar to the formula below but I need unique values.

=COUNTIFS(Sheet1!E:E,A2,Sheet1!F:F,B2,Sheet1!G:G,C2,Sheet1!K:K,E2)

Thank in advance.

 

[Marcelo Branco]

Welcome to Mr Excel forum

See if this example helps

	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​	O​
1​	Val1​	Val2​	Val3​				ID​	Criteria1​	Criteria2​	Criteria3​	Result​
2​	a​	b​	c​				ID1​	a​	b​	c​	3​
3​	a​	b​	d​				ID2​
4​	a​	b​	c​				ID1​
5​	a​	b​	c​				ID3​
6​	a​	b​	c​				ID4​
7​	a​	e​	c​				ID5​
8​	a​	b​	c​				ID4​

<tbody>
</tbody>

Criteria in columns L:N

Array formula in O2
=SUM(IF(FREQUENCY(IF(E2:E8=L2,IF($F$2:$F$8=M2,IF(G2:G8=N2,MATCH(K2:K8,K2:K8,0)))),ROW(K2:K8)-ROW(K2)+1),1))
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[KARPRI11]

Hello Marcelo
I tried the CSE formula below. The value data resides in sheet1 and the criteria data resides in worksheet where I want the count results. I got no results , please review and advise.

=SUM(IF(FREQUENCY(IF(sheet1!E:E,A2,IF(sheet1!F:F,B2,IF(sheet1!G:G,C2,MATCH(sheet1!K:K,sheet1!K:K,0)))),ROW(sheet1!K:K)-ROW(SHEET1!K2)+1),1))

 

[Marcelo Branco]

KARPRI11

Avoid references to entire columns, like E:E, F:F etc, in array formulas. Use definite ranges - i used rows 2:100 in the formula below - adjust to suit.

Try
=SUM(IF(FREQUENCY(IF(Sheet1!E2:E100=A2,IF(Sheet1!F2:F100=B2,IF(Sheet1!G2:G100=C2,IF(Sheet1!K2:K100<>"",MATCH(Sheet1!K2:K100,Sheet1!K2:K100,0))))),ROW(Sheet1!K2:K100)-ROW(Sheet1!K2)+1),1))
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[KARPRI11]

Hello Marcelo
Many thanks! This CSE formula worked. I will leverage this formula for future summary reports.

=SUM(IF(FREQUENCY(IF(Sheet1!E2:E15549=A2,IF(Sheet1!F2:F15549=B2,IF(Sheet1!G2:G15549=C2,IF(Sheet1!K2:K15549<>"",MATCH(Sheet1!K2:K15549,Sheet1!K2:K15549,0))))),ROW(Sheet1!K2:K15549)-ROW(Sheet1!K2)+1),1))

 

[Marcelo Branco]

You are welcome. Glad to help.

M.

 

[KARPRI11]

Hello Marcelo
I am trying to add to the formula listed below. Is there away to identify the count of rows where column f:f14719 = "Open". The formula below gives me a count of unique tool IDs now I need a count of unique tool IDs where column f:f1471 = "Open". Thanks in advance.
.
=SUM(IF(FREQUENCY(IF(Sheet1!A2:A14719=A4,IF(Sheet1!B2:B14719=B4,IF(Sheet1!C2:C14719=C4,IF(Sheet1!E2:E14719<>"",MATCH(Sheet1!E2:E14719,Sheet1!E2:E14719,0))))),ROW(Sheet1!E2:E14719)-ROW(Sheet1!E2)+1),1))