Count Staff and Rating value totals - Formula help

darrenmccoy

New Member
Joined
Mar 26, 2019
Messages
21
Can you guys help me work out the formulas to calculate the totals of each value that each staff member has?
This example shows the count of the staff name appearing in Cloumn C and D, the the total times the Rating value appears against each staff name.
I have 42,000 records to run this over, can you help and suggest an efficient way to create this report?

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
RATINGSTAFFSTAFFCountCannot ValidateValidatedDoes Not Match
Cannot ValidateJohn SmithAlex Shank4valuevaluevalue
Cannot ValidateTim SmithElise Mong2valuevaluevalue
Cannot ValidateRob JonsonJamie Mile4valuevaluevalue
Cannot ValidateJamie MileJohn Smith4valuevaluevalue
ValidatedTina TweetRob Jonson2valuevaluevalue
ValidatedWalter DroneTim Smith4valuevaluevalue
Does Not MatchTim SmithTina Tweet2valuevaluevalue
Does Not MatchAlex ShankWalter Drone4valuevaluevalue
Cannot ValidateAlex Shank
Cannot ValidateJamie Mile
Does Not MatchElise Mong
Cannot ValidateJohn Smith
Does Not MatchWalter Drone
Does Not MatchJohn Smith
Does Not MatchTim Smith
ValidatedRob Jonson
Cannot ValidateJamie Mile
ValidatedTina Tweet
ValidatedWalter Drone
Does Not MatchTim Smith
Cannot ValidateAlex Shank
Does Not MatchAlex Shank
Does Not MatchJamie Mile
ValidatedElise Mong
Does Not MatchJohn Smith
Does Not MatchWalter Drone

<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="51"><col width="116"><col width="100"><col width="100"></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
942
Office Version
2007
Platform
Windows
This formula does it for you. I have assumed your data starts in A1 so E2 would be the first value (for Alex Shank - Cannot Validate count). Input this formula into E2 then drag across and down.

In E2: =COUNTIFS($B:$B,$C2,$A:$A,E$1)
 
Last edited:

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
942
Office Version
2007
Platform
Windows
For completeness, I assume you already have this formula in D2 and dragged down? =COUNTIF(B:B,C2)
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,318
maybe something like this with PowerQuery

sourceresult
RATINGSTAFFSTAFFCountCannot ValidateDoes Not MatchValidated
Cannot ValidateJohn SmithAlex Shank
4​
2​
2​
0​
Cannot ValidateTim SmithElise Mong
2​
0​
1​
1​
Cannot ValidateRob JonsonJamie Mile
4​
3​
1​
0​
Cannot ValidateJamie MileJohn Smith
4​
2​
2​
0​
ValidatedTina TweetRob Jonson
2​
1​
0​
1​
ValidatedWalter DroneTim Smith
4​
1​
3​
0​
Does Not MatchTim SmithTina Tweet
2​
0​
0​
2​
Does Not MatchAlex ShankWalter Drone
4​
0​
2​
2​
Cannot ValidateAlex Shank
Cannot ValidateJamie Mile
Does Not MatchElise Mong
Cannot ValidateJohn Smith
Does Not MatchWalter Drone
Does Not MatchJohn Smith
Does Not MatchTim Smith
ValidatedRob Jonson
Cannot ValidateJamie Mile
ValidatedTina Tweet
ValidatedWalter Drone
Does Not MatchTim Smith
Cannot ValidateAlex Shank
Does Not MatchAlex Shank
Does Not MatchJamie Mile
ValidatedElise Mong
Does Not MatchJohn Smith
Does Not MatchWalter Drone

Code:
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"STAFF"}, {{"Count", each Table.RowCount(_), type number}, {"CAR", each _, type table}}),
    Expand = Table.ExpandTableColumn(Group, "CAR", {"RATING"}, {"RATING"}),
    Duplicate = Table.DuplicateColumn(Expand, "Count", "Count - Copy"),
    Pivot = Table.Pivot(Duplicate, List.Distinct(Duplicate[RATING]), "RATING", "Count - Copy", List.NonNullCount)
in
    Pivot[/SIZE]
 

Watch MrExcel Video

Forum statistics

Threads
1,099,029
Messages
5,466,116
Members
406,467
Latest member
bpat83

This Week's Hot Topics

Top