Count cells containing criteria and another cell criteria but ignore duplicates

[trebor1956]

Hi,

I am trying to count cells in a range B2:B16 containing "KS*" but ignoring any duplicates if cell A2:A16 contain 1

ie. how many instances of KS* that are in zone 1. the answer should be 4 but I can't work out how to do it.

Can someone please help.

zone​	switch
1​	ks55
1​	ks65
1​	ks57
1​	ks55
1​	ks59
1​	ks55
2​	ks61
3​	ks62
2​	ks63
4​	ks64
1​	ks65
2​	ks65
4​	ks65
5​	ks65
1​	ks65

 

[Eric W]

Try:

Book3
	A	B	C	D	E	F
1	zone	switch		Zone	Unique	Unique (dynamic formula)
2	1	ks55		1	4	4
3	1	ks65
4	1	ks57
5	1	ks55
6	1	ks59
7	1	ks55
8	2	ks61
9	3	ks62
10	2	ks63
11	4	ks64
12	1	ks65
13	2	ks65
14	4	ks65
15	5	ks65
16	1	ks65
Sheet12

Cell Formulas
Range	Formula
E2	E2	=SUMPRODUCT(SIGN(FREQUENCY(IF(A2:A16=D2,MATCH(B2:B16,B2:B16,0)),ROW(B2:B16)-ROW(B2)+1)))
F2	F2	=ROWS(UNIQUE(FILTER(B2:B16,A2:A16=D2)))

 

[trebor1956]

Try:

Book3
	A	B	C	D	E	F
1	zone	switch		Zone	Unique	Unique (dynamic formula)
2	1	ks55		1	4	4
3	1	ks65
4	1	ks57
5	1	ks55
6	1	ks59
7	1	ks55
8	2	ks61
9	3	ks62
10	2	ks63
11	4	ks64
12	1	ks65
13	2	ks65
14	4	ks65
15	5	ks65
16	1	ks65
Sheet12

Cell Formulas
Range	Formula
E2	E2	=SUMPRODUCT(SIGN(FREQUENCY(IF(A2:A16=D2,MATCH(B2:B16,B2:B16,0)),ROW(B2:B16)-ROW(B2)+1)))
F2	F2	=ROWS(UNIQUE(FILTER(B2:B16,A2:A16=D2)))

Try:

Book3
	A	B	C	D	E	F
1	zone	switch		Zone	Unique	Unique (dynamic formula)
2	1	ks55		1	4	4
3	1	ks65
4	1	ks57
5	1	ks55
6	1	ks59
7	1	ks55
8	2	ks61
9	3	ks62
10	2	ks63
11	4	ks64
12	1	ks65
13	2	ks65
14	4	ks65
15	5	ks65
16	1	ks65
Sheet12

Cell Formulas
Range	Formula
E2	E2	=SUMPRODUCT(SIGN(FREQUENCY(IF(A2:A16=D2,MATCH(B2:B16,B2:B16,0)),ROW(B2:B16)-ROW(B2)+1)))
F2	F2	=ROWS(UNIQUE(FILTER(B2:B16,A2:A16=D2)))

Hi Eric,

Thank you for the solutions. I presume I can use either? I have tried them both and the first gives a result of 1 the other says "this function not valid" - any ideas?

 

[bebo021999]

Book1
	A	B	C	D
1	zone	switch	Zone	Unique
2	1	ks55	1	4
3	1	ks65
4	1	ks57
5	1	ks55
6	1	ks59
7	1	ks55
8	2	ks61
9	3	ks62
10	2	ks63
11	4	ks64
12	1	ks65
13	2	ks65
14	4	ks65
15	5	ks65
16	1	ks65
Sheet1

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT(IF($A$2:$A$16=C2,IF(LEFT($B$2:$B$16,2)="ks",1/COUNTIFS($A$2:$A$16,C2,$B$2:$B$16,"ks*",$B$2:$B$16,$B$2:$B$16),"")))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Eric W]

Hi Eric,

Thank you for the solutions. I presume I can use either? I have tried them both and the first gives a result of 1 the other says "this function not valid" - any ideas?

It depends on which version of Excel you're using. As Fluff mentioned, if you could update your profile, we could be a bit more targeted with our suggestions.

The first formula should work for you, but you need to enter it with Control+Shift+Enter, not just Enter. The second formula probably won't work for you since it uses functions only available in Excel 365 or Excel 2021, and the error message you're getting seems to indicate you don't have them.