[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 192
  • Last Modified:

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
0
Jim Horn
Asked:
Jim Horn
1 Solution
 
StephenJRCommented:
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now