CODEWITHSUNDEEP
arraysif-statementgoogle-sheetscountmax
Stuart Bell
Stuart Bell

Reputation: 59

Count the number of cells from 1st blank and start count again after next blank

https://docs.google.com/spreadsheets/d/1anfWAkxa73-N7TydeMXVRNTuA1UB-vJ9A3iEnDjXa-I/edit?usp=sharing

Sheet = Count

I have column A with a list of Author. Interactions the first A2 is blank and should be counted as 1 in B2. A3 should then result in B3 showing 2. A4 should result in B4 showing 3. A5 should result in B5 showing 4. A6 should result in B5 showing 4. A6 should result in B6 showing 5. A7 is blank and should result in the the count starting again, therefore B7 should be 1, B8 = 2, B9 = 3, b10 = 4, b11 = 5, b12 = 6. B13 starts again in the test data.

The formula I am using is: =ArrayFormula(MIN(IF(A2:A="",ROW(A2:A)-ROW(A2)+1))-1)

This formula counts the wrong way as in 3,2,1 instead of 1,2,3 and also gives the blank cell 0 when it should be 1.

Any ideas?

Upvotes: 2

Views: 64

Answers (1)

player0
player0

Reputation: 1

use:

=ARRAYFORMULA(ARRAY_CONSTRAIN(COUNTIFS(COUNTIFS(A2:A, "", 
 ROW(A2:A), "<="&ROW(A2:A)), COUNTIFS(A2:A, "", 
 ROW(A2:A), "<="&ROW(A2:A)), ROW(A2:A), "<="&ROW(A2:A)), 
 MAX((A2:A<>"")*(ROW(A2:A)))-1, 1))

enter image description here

Upvotes: 3

Related Questions