sir laurance

Q: Can you assign a numerical value to text

I have a roster with 8 names on it, either 2 or 3 different names per day. At the bottom of the sheet I would like to show a total for each person how many times they are entered per month.

 

Is there a way of counting these names and having an individual total for each?

MacBook Pro (Retina, 15-inch, Mid 2014), OS X El Capitan (10.11.6)

Posted on Aug 31, 2016 6:10 AM

Close

Q: Can you assign a numerical value to text

  • All replies
  • Helpful answers

  • by Wayne Contello,Solvedanswer

    Wayne Contello Wayne Contello Aug 31, 2016 6:35 AM in response to sir laurance
    Level 6 (19,007 points)
    iWork
    Aug 31, 2016 6:35 AM in response to sir laurance

    Here is a way to do this:

    Screen Shot 2016-08-31 at 8.31.16 AM.png

     

    In the table where you have the names (I named the table "Name List")

    add a new column titled "Name ID" as shown.

     

    Make the first row a header row

     

    select cell B2 and type (or copy and paste from here) the formula:

    =IF(COUNTIF($A$2:A2,A2)=1, MAX($B$1:B1)+1, "")

     

    shorthand for this is:

    B2=IF(COUNTIF($A$2:A2,A2)=1, MAX($B$1:B1)+1, "")

     

    select cell B2, copy

    select cells B2 thru the end of column B, paste

     

    Now create a second table:

    Screen Shot 2016-08-31 at 8.34.35 AM.png

     

    I named the second table "Name Summary".

     

    make the first row a header row

     

    A2=IFERROR(OFFSET(Name List::$A$2,MATCH((ROW()−1), Name List::B,0)−2, 0), "")

    B2=IF(A2≠"",COUNTIF(Name List::A,A2), "")

     

    select cells A2 thru B2, copy

    select cells A2 thru the end of column B, paste

  • by sir laurance,

    sir laurance sir laurance Sep 1, 2016 12:17 AM in response to Wayne Contello
    Level 1 (4 points)
    iWork
    Sep 1, 2016 12:17 AM in response to Wayne Contello

    Thanks for that.

     

    I would never of thought it was so easy!

  • by Wayne Contello,

    Wayne Contello Wayne Contello Sep 1, 2016 4:42 AM in response to sir laurance
    Level 6 (19,007 points)
    iWork
    Sep 1, 2016 4:42 AM in response to sir laurance

    Numbers is a good program.  Take time to study the formulas and understand what they are doing.  Ask questions here to learn more.

     

    You are very welcome.