Forum Discussion

Cujo5's avatar
Cujo5
Copper Contributor
Sep 08, 2022

Formula to search names with numbers next to them and add

Cant find a configuration to Sum up Names with numbers next to them.

My list goes like:

joe 1

joe 1

jim 1

jim 1

 

And so on. I need to just take all instances of "joe 1" and add the numbers so that i end up with a total like "joe 2"

or even just "2" as an added sum of all instances of the person and number in a separate cell.

any help is appreciated! OR a suggestion on an easier way that will work.

excel
Formulas and Functions

2 Replies

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Sep 08, 2022

    Cujo5 It seems you are just trying to count each unique name. Formula I have used as per screenshot.

    B2=LEFT(UNIQUE(A2:A6),SEARCH(" ",UNIQUE(A2:A6)))
    C2=COUNTIFS(A2:A6,"*"&B2#&"*")

     

     

    • Cujo5's avatar
      Cujo5
      Copper Contributor
      Sep 08, 2022

      Harun24HR 

      Yes sort of. The only other part is if I have different numbers with the names like Joe 2 

      Joe 5 

      Joe 7

      Jim 1

      Jim 1

       

      Will the formula still add the numbers that are with the name or only add the number of times the name shows?

Resources