Forum Discussion

Comsubin's avatar
Comsubin
Copper Contributor
Nov 12, 2025

Problem creating an Excel formula

I am contacting you because I am having trouble creating a formula to count names in an Excel spreadsheet.

I have created a schedule in Excel in which there is usually one name per cell. The calculation of these simple cells is fine.

However, in cells where there are two names written ‘NAME F.1 / NAME F.2’, only the first name is counted.

I do not know how to make the second name count as well.

If necessary, I can send you the Excel file.

 

Thank you for your help, as I do not know how else to solve this problem.

community
excel
Formulas and Functions

3 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Nov 12, 2025
    = LET(
    list, TOCOL(Table1[names],1),
    count, MAP(list, WORDCOUNTλ),
    SUM(count)
  )

where

WORDCOUNTλ = LAMBDA(item, 
    COUNTA(REGEXEXTRACT(item, "\w+", 1))
);

     

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Nov 12, 2025

    Hi

    It's always better to share a file, and to mention the version of Excel in use

    Assuming you run 365 or Excel Web

    in E3:

    =REDUCE(0, B3:B12,
  LAMBDA(init,name,
    init + IF( name <> "", COUNTA( TEXTSPLIT( name, "/",, TRUE ) ) )
  )
)

    alternatively:

    =REDUCE(0, TOCOL( B3:B12, 1),
  LAMBDA(init,name,
    init + COUNTA( TEXTSPLIT( name, "/",, TRUE ) )
  )
)

     

    • m_tarler's avatar
      m_tarler
      Bronze Contributor
      Nov 12, 2025

      another option:

      =ROWS(TEXTSPLIT(TEXTJOIN("/",1,A1:A10),,"/",1))

      where A1:A10 is the range to check

      note this may fail if you have a very large list and reach the total text string length limit (~32k which is about 1.6k names if they average 20 characters each)

Resources