Counting items in a range and outputting to a column in a pattern-CodePudding

I have a range like this, chunks of text items separated by blank spaced rows

Source

        A           B           C     

1       mercury     apple       SET A 
2       mars        mars              
3       jupiter     jupiter           
4       venus       haha              
5       saturn      saturn            
6                                     
7       jill        six         SET B 
8       earth       jill              
9       nine        earth             
10      ten         nine              
11                                    
12      thirteen    eleven      SET C 
13      fourteen    nepture           
14      sarah       thirteen          
15      sixteen     fourteen          
16      seventeen   sarah             
17                                    
18      nineteen    sixteen     SET D 
19      twenty      seventeen         
20

I would like to add another column D which counts theh chunks of items and shows the count number in a repeated fashion, like so

Desired result, Col D

        A           B           C       D 

1       mercury     apple       SET A   1 
2       mars        mars                1 
3       jupiter     jupiter             1 
4       venus       haha                1 
5       saturn      saturn              1 
6                                         
7       jill        six         SET B   2 
8       earth       jill                2 
9       nine        earth               2 
10      ten         nine                2 
11                                        
12      thirteen    eleven      SET C   3 
13      fourteen    nepture             3 
14      sarah       thirteen            3 
15      sixteen     fourteen            3 
16      seventeen   sarah               3 
17                                        
18      nineteen    sixteen     SET D   4 
19      twenty      seventeen           4

I've tried, but struggling, using various approaches via VLOOKUP, QUERY COUNTA etc and have explored FLATTEN but could really use some help here.

I can already do that via Apps Script, but I'd really like a formula based approach.

CodePudding user response：

try:

=INDEX(LAMBDA(c, IF(LEN(A:A&B:B), VLOOKUP(ROW(c), IF(c<>"", {ROW(c), 
 COUNTIFS(c, "<>", ROW(c), "<="&ROW(c))}), 2, 1), ))(C:C))