Count by unique criteria in a separate column

[cabaragoya]

I have dataset similar to below. I would like to count Status by unique Product ID.



Once there is a confirmed status on any version in a Product ID, the Product ID should count as "Confirmed", even if there are other versions that are marked "Not Started" or "Pending". (For example, Product 1000, 1004, and 1006 should count as "Confirmed".)

Similarly, if there is not a "Confirmed" status, but there is a "Pending" status on any version within a Product ID, that product should count as "Pending", even if there are other versions that are "Not Started". (For example, Product 1002 should count as "Pending".)

Ideally, the count would end up looking similar to this table:


What formulas can I write in the table to count the Confirmed, Pending, and Not Started statuses as described?

 

[bebo021999]

Formula base solution with a helper column

Bảng tính số tháng.xlsx
	A	B	C	D	E	F	G
1				Helper			Count
2	1000	a	Confirmed	Confirmed		Confirmed	3
3	1000	b	Confirmed	Confirmed		Pending	1
4	1000	c	Not Started	Confirmed		Not Started	2
5	1000	d	Not Started	Confirmed
6	1002	e	Pending	Pending
7	1002	f	Not Started	Pending
8	1002	g	Not Started	Pending
9	1002	h	Not Started	Pending
10	1003	j	Not Started	Not Started
11	1003	i	Not Started	Not Started
12	1003	k	Not Started	Not Started
13	1003	l	Not Started	Not Started
14	1004	m	Confirmed	Confirmed
15	1004	n	Confirmed	Confirmed
16	1004	o	Confirmed	Confirmed
17	1004	p	Pending	Confirmed
18	1004	q	Pending	Confirmed
19	1005	r	Not Started	Not Started
20	1005	s	Not Started	Not Started
21	1005	t	Not Started	Not Started
22	1005	u	Not Started	Not Started
23	1006	v	Confirmed	Confirmed
24	1006	w	Pending	Confirmed
25	1006	x	Not Started	Confirmed
26	1006	y	Not Started	Confirmed
27	1006	z	Not Started	Confirmed
Sheet2

Cell Formulas
Range	Formula
F2	F2	=C2
G2:G4	G2	=SUMPRODUCT(IF($D$2:$D$27=$F2,1/COUNTIFS($D$2:$D$27,$F2,$A$2:$A$27,$A$2:$A$27)))
F3:F4	F3	=C6
D2:D27	D2	=IF(COUNTIFS($A$2:$A$27,A2,$C$2:$C$27,"Confirmed"),"Confirmed",IF(COUNTIFS($A$2:$A$27,A2,$C$2:$C$27,"Pending"),"Pending","Not Started"))
Press CTRL+SHIFT+ENTER to enter array formulas.

Without helper, would offer an UDF (via VBA) solution. Call me back if you interested in.

 

[alansidman]

Here is a Power Query solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type", {"Column1", "Column3"}),
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Column3"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}})
in
    #"Grouped Rows"

 

[Peter_SSs]

Formula solution without helper col

23 03 09.xlsm
	A	B	C	D	E	F	G
1							Count
2	1000	a	Confirmed			Confirmed	3
3	1000	b	Confirmed			Pending	1
4	1000	c	Not Started			Not Started	2
5	1000	d	Not Started
6	1002	e	Pending
7	1002	f	Not Started
8	1002	g	Not Started
9	1002	h	Not Started
10	1003	j	Not Started
11	1003	i	Not Started
12	1003	k	Not Started
13	1003	l	Not Started
14	1004	m	Confirmed
15	1004	n	Confirmed
16	1004	o	Confirmed
17	1004	p	Pending
18	1004	q	Pending
19	1005	r	Not Started
20	1005	s	Not Started
21	1005	t	Not Started
22	1005	u	Not Started
23	1006	v	Confirmed
24	1006	w	Pending
25	1006	x	Not Started
26	1006	y	Not Started
27	1006	z	Not Started
Count

Cell Formulas
Range	Formula
G2	G2	=COUNT(UNIQUE(FILTER(A2:A27,C2:C27=F2,"")))
G3	G3	=COUNT(UNIQUE(FILTER(A2:A27,(C2:C27=F3)*(COUNTIFS(A2:A27,A2:A27,C2:C27,F2)=0),"")))
G4	G4	=COUNT(UNIQUE(FILTER(A2:A27,(C2:C27=F4)*(COUNTIFS(A2:A27,A2:A27,C2:C27,F2)+COUNTIFS(A2:A27,A2:A27,C2:C27,F3)=0),"")))