Formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-3301127%22%20slang%3D%22en-US%22%3EFormula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3301127%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20I'm%20trying%20to%20put%20together%20a%20formula%20that%26nbsp%3Btells%20me%20how%20many%20times%20an%20individual%20caused%20fines%201%20through%2010.%20E.g%20Matt%20caused%20fine%2010%204%20times%2C%20fine%208%202%20times%20but%20fine%201%2C%202%2C%203%2C%204%2C%205%2C%206%2C%207%20and%209%2C%200%20times.%3C%2FP%3E%3CP%3EThe%20idea%20is%20that%20over%20a%20period%20of%20time%20we%20can%20see%20if%20there%20is%20any%20patterns%20with%20that%20individual%20and%20take%20the%20required%20actions%20to%20address%20the%20problem%20causing%20the%20fine.%20Sometimes%2C%20also%20very%20rarely%20a%20person%20can%20caused%202%20fines%20in%20the%20same%20period%20so%20it%20will%20need%20to%20include%20this%20column%20too.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202022-05-03%20at%205.30.44%20pm.png%22%20style%3D%22width%3A%20274px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F368770i2F5CC911E8972A22%2Fimage-dimensions%2F274x234%3Fv%3Dv2%22%20width%3D%22274%22%20height%3D%22234%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202022-05-03%20at%205.30.44%20pm.png%22%20alt%3D%22Screen%20Shot%202022-05-03%20at%205.30.44%20pm.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3301127%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3301182%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3301182%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1360540%22%20target%3D%22_blank%22%3E%40cheeseontoast101%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20also%20a%20fairly%20standard%20problem%20for%20a%20Excel%20365%20array%20solution%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20LET(%0A%20%20r%2C%20SORT(UNIQUE(Resp))%2C%0A%20%20f%2C%20TRANSPOSE(SORT(UNIQUE(Fine)))%2C%0A%20%20COUNTIFS(Fine%2Cf%2CResp%2Cr))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3301129%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3301129%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1360540%22%20target%3D%22_blank%22%3E%40cheeseontoast101%3C%2FA%3E%26nbsp%3BWouldn't%20a%20pivot%20table%20work%3F%20Name%20in%20the%20Row%20field%2C%20Fine%20reason%20in%20the%20Column%20field%20and%20Fine%20reason%20in%20the%20Value%20field%20(as%20count).%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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.

 

Screen Shot 2022-05-03 at 5.30.44 pm.png

 

 

 

2 Replies

@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).

@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))