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
Here is a way to do this:
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:
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
Posted on Aug 31, 2016 6:35 AM

