COUNTIFS - multiple resets on value on GoogleSheets

[Lightkeeper]

I have a fictive criminal register and would like to count number of points on the driver's license. I have a COUNTIFS that looks up the social security number from H3. D has the social security number and V has "1 point", "2 points" or "Suspension" (3 points yields suspension). Here I have COUNTIFS to sum the number of points - I multiply the 2 points with 2 because it shall counts "2 points" as 2. I hope you understand.
My code is as follows:

=[FONT=Verdana]IF(COUNTIFS(D:D;H3;V:V;"1 point")+COUNTIFS(D:D;H3;V:V;"2 points")*2>2;"0";COUNTIFS(D:D;H3;V:V;"1 point")+COUNTIFS(D:D;H3;V:V;"2 points")*2[/FONT]

Now, I would like a check that resets the count on either of these conditions:

- If the points reaches 3 it should reset to 0. Let's say that Monday the person gets a point, and Wednesday he gets 2 points. This would result in 3 points and suspension. Hereby the count is reset and the person would have one point again on next violation.
- If there's a Suspension the count also resets. If the person gets a point and then a Suspension the next day, the count resets again and he starts with a clean slate.

All conditions should of course be in chronological order.


It must be a formula for one cell. Copy-down is not an option. So basically it changes the cell accordingly. 0, 1, or 2 points on drivers license.

The code must be for Google Sheets because the database is used by several users.

I hope you can help with this. Thank you so much

 

[Lightkeeper]

Re: COUNTIFS - multiple resets on value

I think I need to explain more. The reason I use 1 point and 2 points is because the data comes from a Google Form. It's a system for GTA V RP where I'm in the police force. The only way to make the sheet writeable without having people messing up the code is via a form.

On one page the cop types the social security number of the plaintiff. They then picks the violation. Traffic, drugs, etc. And if they make a traffic violation the plaitiff could get 1 point or 2 points on their drivers licence - or even Suspension. It's just a multiple choice from the form. Everything is being sent to a sheet and that data is then copied to a new sheet where I take care of all the formatting and formulas. Here's an example of how it writes the multiple choice:

it's in Danish, but "Ubetinget frakendelse" is "Suspension" and "1 klip" is in this case "1 point". That said it is possible to convert the data to my liking by creating a new column where I can format the output to a more code friendly result.

And the formatted version:

.

Now the reason I use D:D and V:V is because the number of rows always changes. And I don't know how to make it variable.

I know about the MOD-formula but I need something more than that. First of all it must only count the number points for the particular person and his or hers social security number. And then it goes through the column and counts the points chronological. Here's an example:

03-12-2018 @ 19:45 - Suspension (points reset to 0)
04-12-2018 @ 18:55 - 1 point (1 point in total)
09-12-2018 @ 00:56 - 1 point (2 points in total)
09-12-2018 @ 16:55 - 1 point (3 points in total = Suspension - points reset)
23-12-2018 @ 15:35 - 1 point (1 point in total)
23-12-2018 @ 23:47 - 2 points (3 points in total = Suspension - points reset)
24-12-2018 @ 09:59 - 1 point (1 point in total)
24-12-2018 @ 10:30 - Suspension (points reset to 0)

All that happens on the fly and returns to one cell with the value 0, 1, or 2.

Please let me know if you have anymore questions.

 

[Lightkeeper]

Re: COUNTIFS - multiple resets on value

I used this code:

[COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]MOD[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]SUMIF[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]D117:D10000[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata];[/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]H3[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata];[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]W117:W10000[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata];[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]3[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR]

[FONT=Arial, Helvetica, sans-serif]However, it only works with points. It adds up the points correctly but the red hearing is the Suspension (3 points). I totally agree that if the person has two points and receives a third, he will have zero points on their license - because it's suspended. However, on direct Suspension (3 points) it should in all cases set to zero. Right now it will return 1 point when the person has 1 point, 2 points when the person has 2 points and so on. Can you help me figure out how to make that check?[/FONT]

 

[Lightkeeper]

Re: COUNTIFS - multiple resets on value

This has been solved. Go ahead and delete/close thread.