Need the formula for counting names in one column + counts in other columns

Given this series of data:

Person1   yes
Person1   yes
Person1
Person1
Person2   yes
Person2  
Person2
Person3   yes
Person3   yes
Person3   yes

I would like to create a function that returns the below recordset
(Columns are Person Name, Count of Yes, Count of all rows for that person)
Person1  2  4
Person2  1  3
Person3  3  3

Thanks in advance.
Jim
LVL 66
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
StephenJRConnect With a Mentor Commented:
What about a pivot table? Add a header row, Name in the row field, Count of Yes and Count of Name in the data field.
0
 
Mattijs33Commented:
You can use COUNT and COUNTIF
I have added a Dutch example. COUNT = AANTAL, COUNTIF = AANTALLEN.ALS
count.xlsx
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
I'd follow StephenJR's suggestion and use a pivot table. The benefit is that you do not have to create the list of unique names manually. Drag the Name into the row area, and into the Values area drag Name (again) and Status. Set both value fields to Count. See attached,

cheers, teylyn
pivot.xlsx
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Thanks.  -Jim
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Thanks guys.    Mattijs33 - I'll explore the example you provided at a later time, and if I have follow-on questions I'll post them and keep you in mind.  -Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.