Forum Discussion
formula to count the number of cells including the one which repeats itself
Terence019740 If you are using Excel for MS365 or Excel for the web, the SCAN function could be used as follows:
=LET( rng, E6:E200, arr, TEXTAFTER(SCAN("1|", SEQUENCE(ROWS(rng)), LAMBDA(p,n, LET( r, TEXTBEFORE(p, "|"), a, INDEX(rng, r), b, INDEX(rng, n), IF(AND(COUNTIF(a:b, b)=2, b<>""), n+1&"|"&COUNT(a:b), r&"|")))), "|"), IF(arr="", arr, --arr) )With older versions of Excel, try the following formula in cell F6 and copy down as needed:
=IF(AND(COUNTIF(INDEX($E$5:E6, IFNA(MATCH(1E+100, $F$5:F5, 1), 0)+1):E6, E6)=2, E6<>""), COUNT(INDEX($E$5:E6, IFNA(MATCH(1E+100, $F$5:F5, 1), 0)+1):E6), "")See attached...
i will try and explain what it is i would like the formula to do.
1) so i have a column of numbers (Column A) as per below, sometimes there could be a blank space in between them, and or sometimes there could be numerous blank spaces. but what i am interested is in the numbers in Column A.
column COLUMN
A B
Row 1
2 1
3
4 3
5
6
7
8 4
9
10 6
11
12 3 5
13
14 0
15 0 2
16 1
17 3
18 5
19 5 4
20
21
22
23
24
with regards Column A, I would like to ask if there is a formula where it would start counting from the very beginning (A1), and go down the column respectively going through each number individually, until the formula detects a number that repeats itself with any of the above numbers it has already analysed, and that if it does find a number which repeats itself, which in this case it should find that both (A4) & (A12) are the same, then it should display in Column B12 the number 5, signifying that it has counted 5 numbers including that which has caused it to repeat itself.
I would then like for the formula to start counting afresh from A13, down the column until it finds another number that repeats itself, which happens in Column A14 & A15, whereby a zero repeats itself. in this case it should show the number 2, in column B15, to signify that it has counted 2 numbers including the number that repeated itself.
the formula should then start counting fresh from column A16 etc..... until it reaches the end of column A.
I hope I have sort of explained myself, and I again apologize for my lack of excel knowledge.
thank you for your continued support.
Terence Martinez
Terence019740 If you are using Excel for MS365 or Excel for the web, the SCAN function could be used as follows:
=LET(
rng, E6:E200,
arr, TEXTAFTER(SCAN("1|", SEQUENCE(ROWS(rng)), LAMBDA(p,n, LET(
r, TEXTBEFORE(p, "|"),
a, INDEX(rng, r),
b, INDEX(rng, n),
IF(AND(COUNTIF(a:b, b)=2, b<>""), n+1&"|"&COUNT(a:b), r&"|")))), "|"),
IF(arr="", arr, --arr)
)
With older versions of Excel, try the following formula in cell F6 and copy down as needed:
=IF(AND(COUNTIF(INDEX($E$5:E6, IFNA(MATCH(1E+100, $F$5:F5, 1), 0)+1):E6, E6)=2, E6<>""), COUNT(INDEX($E$5:E6, IFNA(MATCH(1E+100, $F$5:F5, 1), 0)+1):E6), "")
See attached...
can i paste excel worksheet which i have done
- It would be better to start a new thread with a new question. That way, all contributors will see it and you'd be more likely to get assistance in a timely manner.
pls help me to create a excel sheet for calculating month wise case done
djclements thank you very much for your help.
it worked.
thank you once again for your support.
terence martinez
Terence019740 You're welcome 🙂
Kudos to OliverScheurich for deciphering the original question before additional clarification was given. My formulas used the same basic logic as his VBA method.
Cheers!
