Count the unique IDs for a table

[zinah]

Hi,

I have the sample table below and what I need to count: the total unique EEID that their type is R

Total Unique ID with conditions.xlsx
	A	B	C	D	E	F	G	H
1	EEID	Type	Questions	Status		Results
2	611	R	Q1	Completed		is to count how many EEID that their Type is R and Completed the Q4	6
3	611	R	Q2	Completed		total of unique EEID that their type is R
4	611	R	Q3
5	611	R	Q4	Completed
6	611	R	Q5	Completed
7	210	NR	Q2	Completed
8	210	NR	Q3
9	210	NR	Q4	Completed
10	210	NR	Q4
11	210	NR	Q5
12	562	R	Q1	Completed
13	562	R	Q2	Completed
14	562	R	Q4	Completed
15	878	R	Q1	Completed
16	878	R	Q2	Completed
17	878	R	Q4	Completed
18	878	R	Q5	Completed
19	658	NR	Q4	Completed
20	775	R	Q1	Completed
21	775	R	Q2	Completed
22	775	R	Q3
23	775	R	Q4
24	830	NR	Q1
25	830	NR	Q2	Completed
26	830	NR	Q3
27	612	R	Q1	Completed
28	612	R	Q2	Completed
29	612	R	Q3
30	612	R	Q4	Completed
31	893	R	Q1	Completed
32	893	R	Q2	Completed
33	893	R	Q3
34	900	NR	Q4	Completed
35	612	R	Q1	Completed
36	612	R	Q2	Completed
37	612	R	Q3
38	612	R	Q4	Completed
39	528	R	Q4	Completed
40	528	R	Q5	Completed
41	919	R	Q1	Completed
42	341	R	Q1	Completed
43	490	NR	Q4	Completed
44	490	NR	Q4
45
46
Sheet2

Cell Formulas
Range	Formula
G2	G2	=COUNTIFS(B2:B44,"R",C2:C44,"Q4",D2:D44,"Completed")

Any suggestions please?

Thank you!

 

[Fluff]

How about

=IFERROR(ROWS(UNIQUE(FILTER(A2:A44,B2:B44="R"))),0)

 

[zinah]

How about

=IFERROR(ROWS(UNIQUE(FILTER(A2:A44,B2:B44="R"))),0)

As usual the first member to jump and help , thank you so much that worked, but what about if I need to add another condition, i.e. (doesn't equal to "WordWord") from the Team column?

Total Unique ID with conditions.xlsx
	A	B	C	D	E	F	G
1	EEID	Type	Questions	Status	Team		Results
2	611	R	Q1	Completed			is to count how many EEID that their Type is R and Completed the Q4
3	611	R	Q2	Completed			total of unique EEID that their type is R
4	611	R	Q3
5	611	R	Q4	Completed
6	611	R	Q5	Completed
7	210	NR	Q2	Completed	WordWord
8	210	NR	Q3		WordWord
9	210	NR	Q4	Completed	WordWord
10	210	NR	Q4		WordWord
11	210	NR	Q5		WordWord
12	562	R	Q1	Completed
13	562	R	Q2	Completed
14	562	R	Q4	Completed
15	878	R	Q1	Completed
16	878	R	Q2	Completed
17	878	R	Q4	Completed
18	878	R	Q5	Completed
19	658	NR	Q4	Completed
20	775	R	Q1	Completed	WordWord
21	775	R	Q2	Completed	WordWord
22	775	R	Q3		WordWord
23	775	R	Q4		WordWord
24	830	NR	Q1
25	830	NR	Q2	Completed
26	830	NR	Q3
27	612	R	Q1	Completed
28	612	R	Q2	Completed
29	612	R	Q3
30	612	R	Q4	Completed
31	893	R	Q1	Completed	WordWord
32	893	R	Q2	Completed	WordWord
33	893	R	Q3		WordWord
34	900	NR	Q4	Completed
35	612	R	Q1	Completed
36	612	R	Q2	Completed
37	612	R	Q3
38	612	R	Q4	Completed
39	528	R	Q4	Completed
40	528	R	Q5	Completed
41	919	R	Q1	Completed
42	341	R	Q1	Completed
43	490	NR	Q4	Completed
44	490	NR	Q4
Sheet2

 

[Fluff]

Like

=IFERROR(ROWS(UNIQUE(FILTER(A2:A44,(B2:B44="R")*(E2:E44<>"Wordword")))),0)

 

[zinah]

Like

=IFERROR(ROWS(UNIQUE(FILTER(A2:A44,(B2:B44="R")*(E2:E44<>"Wordword")))),0)

Thank you so much! It worked perfectly great

 

[Fluff]

You're welcome & thanks for the feedback.