Forum Discussion

TGMAN46's avatar
TGMAN46
Copper Contributor
Feb 27, 2024

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

Excel
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Feb 27, 2024

    TGMAN46 

    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)
)
    • TGMAN46's avatar
      TGMAN46
      Copper Contributor
      Feb 27, 2024
      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.
      • Patrick2788's avatar
        Patrick2788
        Silver Contributor
        Feb 27, 2024

        TGMAN46 

        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)
)

Resources