Counting Unique values with MAX criteria

[dancer5]

Hi there,
I'm trying to find a formula to count the number of unique values that meet criteria, where one of the criteria is a column with a maximum value.

I've tried modifying my UNIQUE(FILTER formula I usually use:
=SUM(--(LEN(UNIQUE(FILTER(A3:A60, (M3:M60=2), "")))>0))
but adding in a criterion for a MAX value has me stuck.
I tried:
=UNIQUE(FILTER(A3:A60, (M3:M60=2)*(MAX(K3:K60)=1)))
and:
=SUM(--(LEN(UNIQUE(FILTER(A3:A60, (M3:M60=2)*(MAX(K3:K60)=1), "")))>0))

but it doesn't work.

Here's an example dataset

- Tables (2024-12-05_1105).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	id										event		event_complete					SHOULD BE
2	1															Number of IDs with 1 Event	#CALC!	1
3	1															Number of IDs with 2 Events	0	2
4	1										1		2			Number of IDs with >=3 Events		1
5	1										2		2
6	1
7	1
8	2
9	2
10	2										1		2
11	2										2		2
12	2										3		2
13	2
14	2
15	2
16	2
17	2
18	3										1		2
19	3										2		2
20	3
21	4
22	4
23	4										1		2
24	4
25	4
26	4
27	4
28	4
29	4
example

Cell Formulas
Range	Formula
Q2	Q2	=UNIQUE(FILTER(A3:A29, (M3:M29=2)*(MAX(K3:K29)=1)))
Q3	Q3	=SUM(--(LEN(UNIQUE(FILTER(A3:A29, (M3:M29=2)*(MAX(K3:K29)=2), "")))>0))

 

[hagia_sofia]

Hello, maybe:

=IFERROR(ROWS(UNIQUE(FILTER($A$2:$A$29,MAXIFS($K$2:$K$29,$A$2:$A$29,$A$2:$A$29)=1))),0)

 

[Cubist]

Try:

Book1
	P	Q
1
2	Number of IDs with 1 Event	1
3	Number of IDs with 2 Events	2
4	Number of IDs with >=3 Events	1
Sheet2

Cell Formulas
Range	Formula
Q2	Q2	=SUM(--(MAXIFS($K$2:$K$29,$A$2:$A$29,UNIQUE($A$2:$A$29))=1))
Q3	Q3	=SUM(--(MAXIFS($K$2:$K$29,$A$2:$A$29,UNIQUE($A$2:$A$29))=2))
Q4	Q4	=SUM(--(MAXIFS($K$2:$K$29,$A$2:$A$29,UNIQUE($A$2:$A$29))>=3))

 

[dancer5]

Thank you both! Both approaches work. I'm using the SUM formula solution.

 

[Cubist]

Thank you both! Both approaches work. I'm using the SUM formula solution.

You're welcome. Glad we can assist.