Count Staff and Rating value totals - Formula help

[darrenmccoy]

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>

RATING	STAFF	STAFF	Count	Cannot Validate	Validated	Does Not Match
Cannot Validate	John Smith	Alex Shank	4	value	value	value
Cannot Validate	Tim Smith	Elise Mong	2	value	value	value
Cannot Validate	Rob Jonson	Jamie Mile	4	value	value	value
Cannot Validate	Jamie Mile	John Smith	4	value	value	value
Validated	Tina Tweet	Rob Jonson	2	value	value	value
Validated	Walter Drone	Tim Smith	4	value	value	value
Does Not Match	Tim Smith	Tina Tweet	2	value	value	value
Does Not Match	Alex Shank	Walter Drone	4	value	value	value
Cannot Validate	Alex Shank
Cannot Validate	Jamie Mile
Does Not Match	Elise Mong
Cannot Validate	John Smith
Does Not Match	Walter Drone
Does Not Match	John Smith
Does Not Match	Tim Smith
Validated	Rob Jonson
Cannot Validate	Jamie Mile
Validated	Tina Tweet
Validated	Walter Drone
Does Not Match	Tim Smith
Cannot Validate	Alex Shank
Does Not Match	Alex Shank
Does Not Match	Jamie Mile
Validated	Elise Mong
Does Not Match	John Smith
Does Not Match	Walter 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>

 

[jmacleary]

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)

 

[jmacleary]

For completeness, I assume you already have this formula in D2 and dragged down? =COUNTIF(B:B,C2)

 

[sandy666]

maybe something like this with PowerQuery

source			result
RATING	STAFF		STAFF	Count	Cannot Validate	Does Not Match	Validated
Cannot Validate	John Smith		Alex Shank	4​	2​	2​	0​
Cannot Validate	Tim Smith		Elise Mong	2​	0​	1​	1​
Cannot Validate	Rob Jonson		Jamie Mile	4​	3​	1​	0​
Cannot Validate	Jamie Mile		John Smith	4​	2​	2​	0​
Validated	Tina Tweet		Rob Jonson	2​	1​	0​	1​
Validated	Walter Drone		Tim Smith	4​	1​	3​	0​
Does Not Match	Tim Smith		Tina Tweet	2​	0​	0​	2​
Does Not Match	Alex Shank		Walter Drone	4​	0​	2​	2​
Cannot Validate	Alex Shank
Cannot Validate	Jamie Mile
Does Not Match	Elise Mong
Cannot Validate	John Smith
Does Not Match	Walter Drone
Does Not Match	John Smith
Does Not Match	Tim Smith
Validated	Rob Jonson
Cannot Validate	Jamie Mile
Validated	Tina Tweet
Validated	Walter Drone
Does Not Match	Tim Smith
Cannot Validate	Alex Shank
Does Not Match	Alex Shank
Does Not Match	Jamie Mile
Validated	Elise Mong
Does Not Match	John Smith
Does Not Match	Walter Drone

[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]

 

[darrenmccoy]

Thank you everybody, this really helped!

 

[sandy666]

You are welcome

Have a nice day