Count Staff and Rating value totals - Formula help

darrenmccoy

darrenmccoy

New Member
Joined
Mar 26, 2019
Messages
37
Office Version
  1. 2019
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>[TABLE="width: 0"]
<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>[TR]
[TD="bgcolor: #D9D9D9"]RATING[/TD]
[TD="bgcolor: #D9D9D9"]STAFF[/TD]
[TD="bgcolor: #B6D7A8"]STAFF[/TD]
[TD="bgcolor: #B6D7A8"]Count[/TD]
[TD="bgcolor: #B6D7A8"]Cannot Validate[/TD]
[TD="bgcolor: #B6D7A8"]Validated[/TD]
[TD="bgcolor: #B6D7A8"]Does Not Match[/TD]
[/TR]
[TR]
[TD]Cannot Validate[/TD]
[TD]John Smith[/TD]
[TD]Alex Shank[/TD]
[TD="align: right"]4[/TD]
[TD="align: center"]value[/TD]
[TD="align: center"]value[/TD]
[TD="align: center"]value[/TD]
[/TR]
[TR]
[TD]Cannot Validate[/TD]
[TD]Tim Smith[/TD]
[TD]Elise Mong[/TD]
[TD="align: right"]2[/TD]
[TD="align: center"]value[/TD]
[TD="align: center"]value[/TD]
[TD="align: center"]value[/TD]
[/TR]
[TR]
[TD]Cannot Validate[/TD]
[TD]Rob Jonson[/TD]
[TD]Jamie Mile[/TD]
[TD="align: right"]4[/TD]
[TD="align: center"]value[/TD]
[TD="align: center"]value[/TD]
[TD="align: center"]value[/TD]
[/TR]
[TR]
[TD]Cannot Validate[/TD]
[TD]Jamie Mile[/TD]
[TD]John Smith[/TD]
[TD="align: right"]4[/TD]
[TD="align: center"]value[/TD]
[TD="align: center"]value[/TD]
[TD="align: center"]value[/TD]
[/TR]
[TR]
[TD]Validated[/TD]
[TD]Tina Tweet[/TD]
[TD]Rob Jonson[/TD]
[TD="align: right"]2[/TD]
[TD="align: center"]value[/TD]
[TD="align: center"]value[/TD]
[TD="align: center"]value[/TD]
[/TR]
[TR]
[TD]Validated[/TD]
[TD]Walter Drone[/TD]
[TD]Tim Smith[/TD]
[TD="align: right"]4[/TD]
[TD="align: center"]value[/TD]
[TD="align: center"]value[/TD]
[TD="align: center"]value[/TD]
[/TR]
[TR]
[TD]Does Not Match[/TD]
[TD]Tim Smith[/TD]
[TD]Tina Tweet[/TD]
[TD="align: right"]2[/TD]
[TD="align: center"]value[/TD]
[TD="align: center"]value[/TD]
[TD="align: center"]value[/TD]
[/TR]
[TR]
[TD]Does Not Match[/TD]
[TD]Alex Shank[/TD]
[TD]Walter Drone[/TD]
[TD="align: right"]4[/TD]
[TD="align: center"]value[/TD]
[TD="align: center"]value[/TD]
[TD="align: center"]value[/TD]
[/TR]
[TR]
[TD]Cannot Validate[/TD]
[TD]Alex Shank[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cannot Validate[/TD]
[TD]Jamie Mile[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Does Not Match[/TD]
[TD]Elise Mong[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cannot Validate[/TD]
[TD]John Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Does Not Match[/TD]
[TD]Walter Drone[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Does Not Match[/TD]
[TD]John Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Does Not Match[/TD]
[TD]Tim Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Validated[/TD]
[TD]Rob Jonson[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cannot Validate[/TD]
[TD]Jamie Mile[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Validated[/TD]
[TD]Tina Tweet[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Validated[/TD]
[TD]Walter Drone[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Does Not Match[/TD]
[TD]Tim Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cannot Validate[/TD]
[TD]Alex Shank[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Does Not Match[/TD]
[TD]Alex Shank[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Does Not Match[/TD]
[TD]Jamie Mile[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Validated[/TD]
[TD]Elise Mong[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Does Not Match[/TD]
[TD]John Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Does Not Match[/TD]
[TD]Walter Drone[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
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:
Upvote 0
For completeness, I assume you already have this formula in D2 and dragged down? =COUNTIF(B:B,C2)
 
Upvote 0
maybe something like this with PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]source[/td][td][/td][td][/td][td]result[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]RATING[/td][td=bgcolor:#5B9BD5]STAFF[/td][td][/td][td=bgcolor:#70AD47]STAFF[/td][td=bgcolor:#70AD47]Count[/td][td=bgcolor:#70AD47]Cannot Validate[/td][td=bgcolor:#70AD47]Does Not Match[/td][td=bgcolor:#70AD47]Validated[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Cannot Validate[/td][td=bgcolor:#DDEBF7]John Smith[/td][td][/td][td=bgcolor:#E2EFDA]Alex Shank[/td][td=bgcolor:#E2EFDA]
4​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]
0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Cannot Validate[/td][td]Tim Smith[/td][td][/td][td]Elise Mong[/td][td]
2​
[/td][td]
0​
[/td][td]
1​
[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Cannot Validate[/td][td=bgcolor:#DDEBF7]Rob Jonson[/td][td][/td][td=bgcolor:#E2EFDA]Jamie Mile[/td][td=bgcolor:#E2EFDA]
4​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]
0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Cannot Validate[/td][td]Jamie Mile[/td][td][/td][td]John Smith[/td][td]
4​
[/td][td]
2​
[/td][td]
2​
[/td][td]
0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Validated[/td][td=bgcolor:#DDEBF7]Tina Tweet[/td][td][/td][td=bgcolor:#E2EFDA]Rob Jonson[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]
0​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Validated[/td][td]Walter Drone[/td][td][/td][td]Tim Smith[/td][td]
4​
[/td][td]
1​
[/td][td]
3​
[/td][td]
0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Does Not Match[/td][td=bgcolor:#DDEBF7]Tim Smith[/td][td][/td][td=bgcolor:#E2EFDA]Tina Tweet[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]
0​
[/td][td=bgcolor:#E2EFDA]
0​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Does Not Match[/td][td]Alex Shank[/td][td][/td][td]Walter Drone[/td][td]
4​
[/td][td]
0​
[/td][td]
2​
[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Cannot Validate[/td][td=bgcolor:#DDEBF7]Alex Shank[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Cannot Validate[/td][td]Jamie Mile[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Does Not Match[/td][td=bgcolor:#DDEBF7]Elise Mong[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Cannot Validate[/td][td]John Smith[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Does Not Match[/td][td=bgcolor:#DDEBF7]Walter Drone[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Does Not Match[/td][td]John Smith[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Does Not Match[/td][td=bgcolor:#DDEBF7]Tim Smith[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Validated[/td][td]Rob Jonson[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Cannot Validate[/td][td=bgcolor:#DDEBF7]Jamie Mile[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Validated[/td][td]Tina Tweet[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Validated[/td][td=bgcolor:#DDEBF7]Walter Drone[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Does Not Match[/td][td]Tim Smith[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Cannot Validate[/td][td=bgcolor:#DDEBF7]Alex Shank[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Does Not Match[/td][td]Alex Shank[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Does Not Match[/td][td=bgcolor:#DDEBF7]Jamie Mile[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Validated[/td][td]Elise Mong[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Does Not Match[/td][td=bgcolor:#DDEBF7]John Smith[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Does Not Match[/td][td]Walter Drone[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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]
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Need formula help for counting Unique Text values with different date criteria
Replies
5
Views
833
Peter_SSs
Peter_SSs
N
  • Question Question
Multiple condition lookup
Replies
5
Views
677
navi_jen
N
J
  • Question Question
Populate Table with filtered data from another table.
Replies
3
Views
3K
DanteAmor
DanteAmor
T
  • Question Question
Index Match Formula with multiple criteria
Replies
3
Views
580
Aryatect
Aryatect
J
  • Question Question
Formula for counting how many times value in cells above are higher.
Replies
9
Views
1K
Jitka
J

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top