Total count of criteria based on each unique ID

[BradleyS]

I would like a total count of items per unique ID. In the image below the total should come to 3, as there are 4 unique IDs, but one unique ID has no data.
It doesn't matter what the data values are in column C. Only that it doesn't count blank cells, i.e. ID 4 has no data in any of the cells in column C so should not be counted.

I just need to find out how many IDs have data regardless of what the dat maybe.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D
1
2	1
3	1	a		3
4	1
5	1
6	2
7	2	2
8	2
9	2	c
10	3
11	3	3
12	3	5
13	3
14	4
15	4
16	4
17	4
Master

Cell Formulas
Range	Formula
D3	D3	=SUM(--(FREQUENCY(IF(B2:B17<>"",MATCH(A2:A17,A2:A17,0)),ROW(A2:A17)-ROW(A2)+1)>0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[BradleyS]

How about

+Fluff 1.xlsm
	A	B	C	D
1
2	1
3	1	a		3
4	1
5	1
6	2
7	2	2
8	2
9	2	c
10	3
11	3	3
12	3	5
13	3
14	4
15	4
16	4
17	4
Master

Cell Formulas
Range	Formula
D3	D3	=SUM(--(FREQUENCY(IF(B2:B17<>"",MATCH(A2:A17,A2:A17,0)),ROW(A2:A17)-ROW(A2)+1)>0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Perfect. How do you do it! I have been trying to work this out for half a day with no success.

I forgot to press Ctrl+Shift+Enter and it still worked. Is it OK if it still works, or is there a reason I should press Ctrl+Shift+Enter regardless?

Thank you

 

[Fluff]

Are you doing this in xl 2010 as per your profile?

 

[BradleyS]

Are you doing this in xl 2010 as per your profile?

Yes, but I also have new versions I may want to use this with. 2010 is my lowest version.

 

[Fluff]

You wouldn't need to array enter the formula in 365 or 2021, but any other version should need Ctrl Shift Enter.

 

[BradleyS]

Thank you for all your help on this. It's very much appreciated

 

[Fluff]

You're welcome & thanks for the feedback.