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.
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.
can you post a screenshot of an example or a file with an example?
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.
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.
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
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
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,
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you.