Dependent Ranking

[Mparka]

Hello, I have a table that looks like
Apple | manual input
Apple | manual input
Apple | manual input
Apple | blank cell
Apple | manual input
Apple | blank cell

This goes down 12 rows for each fruit, then starts a different one. I need a numeric count of each non blank input. For example, I want a third column that has 1,2,3,blank cell,4,blank cell. But when the fruit changes, I want to start the formulas over and go 1-12. I tried doing counta but the blank cells have formulas, and doing range sums doesn’t work because of the variable range. Thanks so much.

 

[moddddmyexcel]

Book1
	A	B	C
1	Apple	test	1
2	Apple
3	Apple	test	2
4	Apple
5	Apple	test	3
6	Apple
7	Apple	test	4
8	Apple
9	Apple	test	5
10	Apple
11	Apple	test	6
12	Apple
Sheet1

Cell Formulas
Range	Formula
C1:C12	C1	=IF(B1<>"",COUNTA($B$1:B1),"")

Something like this?

 

[fjns]

Hi, see the linked file for a possible solution...

The formula used in the table:
=IF(OR(A2="",B2=""),"",IF(MOD(ROW()-1,12)=0,12,MOD(ROW()-1,12))-COUNTIFS(A$2:A2,A2,B$2:B2,""))

Fruits.xlsx

 

[Mparka]

Thanks to both, solved!

 

[fjns]

Hi, I am very glad that the formula works.
If I have enough knowledge, I am happy to help at any time.

 

[bebo021999]

Why not using simple one like this, or I missed something? countif cell A, with B is <>""

Last 5.xlsx
	A	B	C
1
2	Apple	test	1
3	Apple	test	2
4	Apple	test	3
5	Apple
6	Apple	test	4
7	Apple
8	Apple	test	5
9	Apple	test	6
10	Apple	test	7
11	Apple
12	Apple	test	8
13	Apple
14	Orange
15	Orange
16	Orange	abc	1
17	Orange	abc	2
18	Orange	abc	3
19	Orange	abc	4
20	Orange
21	Orange	abc	5
22	Orange
Sheet1

Cell Formulas
Range	Formula
C2:C22	C2	=IF(B2="","",COUNTIFS($A$2:A2,A2,$B$2:B2,"<>"))