# Formula Needed

Copper Contributor

# Formula Needed

I have a need for a formula which allows in the left row A the numbers 1, 2, 3, etc, to be displayed, reflecting the sum of all prior entries in row B plus one. The excel sheet would look like this:

A    B

1    1     Any number or word

2    2     Any number or word

3

4    3    Any number or word

5

6

7    4    Any number or word

5 Replies

# Re: Formula Needed

Try this one:

``````=LET(
r, XMATCH(TRUE, ISTEXT(B:B), , -1),
words, TAKE(B1:B100000, r),
AutoNum, LAMBDA(element,
LET(
keep, ROW(element),
number, COUNTIF(TAKE(words, keep), "*"),
IF(ISBLANK(element), "", number)
)
),
MAP(words, AutoNum)
)``````

# Re: Formula Needed

Wow. Thanks? I'll try it. In the meanwhile, I tried: =COUNTIF(\$F\$6:\$F9,"*") but apparently the asterisk only detects characters but not values such as "\$500.00" which are what I'm trying to detect in the F Column. Is there another character besides the * that can be inserted between the quotes to read values? Many thanks.

# Re: Formula Needed

If it's text or number, then in this context COUNTA is a better choice.

``````=LET(
last_text, XMATCH(TRUE, ISTEXT(B:B), , -1),
last_num, XMATCH(TRUE, ISNUMBER(B:B), , -1),
r, MAX(last_text, last_num),
words, TAKE(B1:B100000, r),
AutoNum, LAMBDA(element,
LET(
keep, ROW(element),
number, COUNTA(TAKE(words, keep)),
IF(ISBLANK(element), "", number)
)
),
MAP(words, AutoNum)
)``````

# Re: Formula Needed

Thanks so much. COUNTA worked. Have a great day.

You're welcome!