Formula help

[cheeseontoast101]

Hi I'm trying to put together a formula that tells me how many times an individual caused fines 1 through 10. E.g Matt caused fine 10 4 times, fine 8 2 times but fine 1, 2, 3, 4, 5, 6, 7 and 9, 0 times.

The idea is that over a period of time we can see if there is any patterns with that individual and take the required actions to address the problem causing the fine. Sometimes, also very rarely a person can caused 2 fines in the same period so it will need to include this column too.

[#2]

@cheeseontoast101 Wouldn't a pivot table work? Name in the Row field, Fine reason in the Column field and Fine reason in the Value field (as count).

[#3]

@cheeseontoast101 

It is also a fairly standard problem for a Excel 365 array solution

= LET(
  r, SORT(UNIQUE(Resp)),
  f, TRANSPOSE(SORT(UNIQUE(Fine))),
  COUNTIFS(Fine,f,Resp,r))