Formula : Count Rows in a list that have a fixed number of unique dependencies in another column

[RobP]

Hi all,

I am trying to find a formula that will count the number of Projects in a Column which has (x) Unique Product codes in another column.

So looking for a formula to go into L14, N14, and P14.

You can see in L19 (I was playing) my logic took me down a path of counting the number of each project, then I would probably discard rows that were LESS THAN the number I was looking for (so discard rows in M that contained 1 or 2 if I was looking for projects that had "3" unique Prod Codes, for answer in N14). Then somehow correlate that to the product codes in col(I) that I would then need to count.

I'm likely over complicating it.. and sending myself down a wrong path .. there's probably a SUMIF that would fix it for me.

Any ideas on how to solve please ?

thanks
Rob

Design Data Master.xlsx
	H	I	J	K	L	M	N	O	P	Q	R
12
13	Count Projects with (x) Number				Proj with 2		Proj with 3		Proj with 4
14	of Product Codes (x = 1 to 10)			Ans :	2	Ans:	3	Ans:	0
15					(prj 2,5)		(prj 4,7,8)	<<< added for problem clarity only
16	Project	Prod Code
17	Project1	AA
18	Project2	AA
19	Project2	AB			Project1	1
20	Project3	AC			Project2	2
21	Project4	AA			Project3	1
22	Project4	AB			Project4	4
23	Project4	AB			Project5	2
24	Project4	AC			Project6	1
25	Project5	AJ			Project7	3
26	Project5	AB			Project8	3
27	Project6	BV
28	Project7	BA
29	Project7	AB
30	Project7	AC
31	Project8	CD
32	Project8	DS
33	Project8	AB
Powerpoint

Cell Formulas
Range	Formula
L19:M26	L19	=LET(prj,UNIQUE(H17:H33),c,SCAN(0,prj,LAMBDA(a,v,COUNTIFS(H17:H33,v))),list,HSTACK(prj,c),list)
Dynamic array formulas.

 

[Fluff]

How about

=LET(g,GROUPBY(H17:H33,I17:I33,COUNTA,,0),ROWS(FILTER(g,TAKE(g,,-1)=2)))

Although I don't see why you are counting Project 4 with 3 codes.

 

[RobP]

Hi Fluff,

sorry, I forgot to add the only caveat that I knew I should have .... my current version on O365 doesn't have the luxury of @Groupby, or @Pivotby for some reason. I think its down to the version that the company has loaded up (they don't tend to upgrade always). So I'm missing playing with that functionality ... sadly.

But I've no doubt of course what you provided will do the job. FYI, Project 4 counts as a 3 as it has 3 Unique Project codes.

Thanks
Rob

 

[Fluff]

Ok, how about

=LET(prj,UNIQUE(H17:H33),c,MAP(prj,LAMBDA(v,ROWS(UNIQUE(FILTER(I17:I33,H17:H33=v))))),SUM(--(c=2)))

 

[RobP]

Thanks again Fluff.

If I put the formula into L14, indeed I get the answer = 2. However, if I put the formula into N14, (I changed the c=2 to c=3 as that was the only immediate obvious link to the number of product codes associated with each project), I get the answer = 0 when it should Answer "3" projects (have 3 unique product codes associated with them)

I'm going to have to take a little time to decipher your formula in order to understand a little more about your approach ...

Cheers
Rob

 

[RobP]

Fluff,

I'm sorry, taking more time on my side, I realised user error I had moved my arrays slightly and didn't correct your formula..

Thats absolutely awesome - thanks a lot.. and never in doubt

Cheers
Rob

 

[Fluff]

You're welcome & thanks for the feedback.