Excel 2003 Counting how many data cells correspond to a specific cell (Person's Name)

**cajodonn** · 06-22-2012, 11:08 AM

I have two columns. Column A has a person's name and Column B has dated notes on that specific person, some names have more notes than others. I was wondering if there is a function similar to count that could see how many note cells associate with a specific name or if you can look up how many dates appear for a specific person, thus giving you the number of notes on them. Any help or advice is greatly appreciated I have a thousand names or so with notes I need to count. Example I attached to this post.

Column1
Name

Durant, Kevin

Column2
Notes

3/8/12-Met with Kevin talked about blah
3/12/2012-Discussed procedures

Column3

Number of Notes
2

**Fotis1991** · 06-22-2012, 11:13 AM

Hi and welcome to the forum.

=COUNTIF(A2:A100,A2)

Or

=SUMPRODUCT((B2:B100<>"")*(A2:A100="Durant, Kevin"))

**NBVC** · 06-22-2012, 11:16 AM

Since your dealing with a mix of merged and unmerged cells, it becomes difficult to do this sort of count.

If you can't unmerge, I would suggest adding helper columns.

So in C2:

=IF(B2="","",LOOKUP(REPT("z",255),A$2:A2))

copied down

in D2 (because you seem to have merged cell with multiple dates, and I will assume a dash means a date):

=LEN(B2)-LEN(SUBSTITUTE(B2,"-",""))

copied down.

Now if you have a name, say Lee, Dan in A20, then to count, you would use:

=SUMIF($C$2:$C$13,$A20,$D$2:$D$13)

and it could be copied down for other names..

**cajodonn** · 06-22-2012, 11:37 AM

Thank you both very much for your quick responses. @ NBVC not sure how to do it but 100 out of 100 on your response saved me a good week or so of work!

**NBVC** · 06-22-2012, 11:44 AM

Glad to have helped reduce your workload.

Kindly mark your thread as Solved if you are satisfied.

Excel 2003 Counting how many data cells correspond to a specific cell (Person's Name)

LinkBack

Thread Tools

Rate This Thread

Display

Excel 2003 Counting how many data cells correspond to a specific cell (Person's Name)

Re: Excel 2003 Counting how many data cells correspond to a specific cell (Person's Name)

Re: Excel 2003 Counting how many data cells correspond to a specific cell (Person's Name)

Re: Excel 2003 Counting how many data cells correspond to a specific cell (Person's Name)

Re: Excel 2003 Counting how many data cells correspond to a specific cell (Person's Name)

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions