COUNTIF WITH UNIQUE COMBINATION

[dannwid]

Hello fellas, I need a little help on countif formula.

I have table with contains No of PO and Vendor Name. Sometimes, some vendors split one PO into 2 or more, hence they appear twice or more.

So, i need to countif every each of vendor by their unique PO. You can see the case in the picture below, I also put the xlsx file.

What combination formula can i use to achieve result in yellow area, with criteria on F4,F5,F6 and so on?

I am hopeful that I describe the case well.

Thank you in advance

EXCEL FILE: DOWNLOAD

 

[hagia_sofia]

Hello, one way to do it could be:

=LET(
a,CHOOSECOLS(UNIQUE(B4:C15),2),
GROUPBY(a,a,COUNTA,,0))

 

[dannwid]

Hello, one way to do it could be:

=LET(
a,CHOOSECOLS(UNIQUE(B4:C15),2),
GROUPBY(a,a,COUNTA,,0))

Not working mate. what is "a" for?

 

[Fluff]

How about

Case 123.xlsx
	A	B	C	D	E	F	G
1
2						formula result goal:
3		PO	VENDORS NAME			VENDOR	Number of POs (unique)
4		123	AAA			AAA	2
5		123	AAA			BBB	1
6		123	AAA			CCC	3
7		123	AAA
8		222	AAA
9		222	AAA
10		333	BBB
11		333	BBB
12		441	CCC
13		442	CCC
14		443	CCC
15		443	CCC
Sheet1

Cell Formulas
Range	Formula
F4:G6	F4	=GROUPBY(C4:C15,B4:B15,LAMBDA(v, ROWS(UNIQUE(v))),,0)
Dynamic array formulas.

 

[dannwid]

How about

Case 123.xlsx
	A	B	C	D	E	F	G
1
2						formula result goal:
3		PO	VENDORS NAME			VENDOR	Number of POs (unique)
4		123	AAA			AAA	2
5		123	AAA			BBB	1
6		123	AAA			CCC	3
7		123	AAA
8		222	AAA
9		222	AAA
10		333	BBB
11		333	BBB
12		441	CCC
13		442	CCC
14		443	CCC
15		443	CCC
Sheet1

Cell Formulas
Range	Formula
F4:G6	F4	=GROUPBY(C4:C15,B4:B15,LAMBDA(v, ROWS(UNIQUE(v))),,0)
Dynamic array formulas.

There is no link to F4? and should i put this formula in F4? My result should be in G4, G5, and G6, hence i think the formulas should be put in G4 G5 G6, no?

 

[Fluff]

The formula goes into a single cell & will spill down & across.

 

[HighAndWilder]

How about

Case 123.xlsx
	A	B	C	D	E	F	G
1
2						formula result goal:
3		PO	VENDORS NAME			VENDOR	Number of POs (unique)
4		123	AAA			AAA	2
5		123	AAA			BBB	1
6		123	AAA			CCC	3
7		123	AAA
8		222	AAA
9		222	AAA
10		333	BBB
11		333	BBB
12		441	CCC
13		442	CCC
14		443	CCC
15		443	CCC
Sheet1

Cell Formulas
Range	Formula
F4:G6	F4	=GROUPBY(C4:C15,B4:B15,LAMBDA(v, ROWS(UNIQUE(v))),,0)
Dynamic array formulas.

Fluff - Where does the value for v come from? Thanks.

 

[Fluff]

It's the values from col B that are associated with col C.

 

[dannwid]

End up with #Name?

what went wrong here?

 

[Fluff]

That suggests you don't have the groupby function yet.
Try

Case 123.xlsx
	B	C	D	E	F	G
2					formula result goal:
3	PO	VENDORS NAME			VENDOR	Number of POs (unique)
4	123	AAA			AAA	2
5	123	AAA			BBB	1
6	123	AAA			CCC	3
7	123	AAA
8	222	AAA
9	222	AAA
10	333	BBB
11	333	BBB
12	441	CCC
13	442	CCC
14	443	CCC
15	443	CCC
16
Sheet1

Cell Formulas
Range	Formula
F4:F6	F4	=UNIQUE(TOCOL(C4:C100,1))
G4:G6	G4	=ROWS(UNIQUE(FILTER($B$4:$B$100,$C$4:$C$100=F4)))
Dynamic array formulas.