Link to home
Start Free TrialLog in
Avatar of exp vg
exp vg

asked on

Excel - Count by Group, Last Name, First Name

I have a spreadsheet, where an individual can be assigned up to four different groups (1 - 4). Column A is Group, Column B is the Last Name, and Column C is the First Name.

The individual is awarded $10 spending budget for each group s/he is in.

Please offer how I can count per individual the number of groups s/he is engaged in (1 - 4 possible).

Thank you.
Avatar of Tj a
Tj a

can you post a screenshot of an example or a file with an example?
Avatar of exp vg

ASKER

Here is an example:

Group                Last Name             First Name
1                            Doe                        John
1                            Smith                     Jane
2                            Doe                        John
2                            Smith                     Jane
3                            Doe                        John
3                            Williams                Mary
4                            Doe                        John

Therefore, the count I would want to see is

Doe John - 4
Smith Jane - 2
Williams Mary - 1

Hope this made sense.

Thank you.
Avatar of exp vg

ASKER

I can use a pivot table - but I am not able to have two  resulting columns as follows (I filter by the GROUP (1 - 4)):

LAST NAME       FIRST NAME    COUNT

Instead, with the pivot table, I see that FIRST NAME becomes a subcategory of LAST NAME that has to be expanded with the + sign

LAST NAME           COUNT
Avatar of exp vg

ASKER

In a sense what I want to see is a three column table, where the last name can have duplicates since more than one person may have the same last name

LAST NAME       FIRST NAME         SUMCOUNT of GROUP
Williams              John                         2 (this person is in Groups 1 and 4)
Williams              Troy                         3 (this person is in Groups 1, 2, 3)

etc,
SOLUTION
Avatar of Tj a
Tj a

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of exp vg

ASKER

Thank you.