COUNTIFS - multiple resets on value


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:

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

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



Selected Answer

Edited Dec 26, 2018  ==================

From your comments below it emerged that the task you wish to assign to the formula you seek is to effectively modify data at the point of entry. E.g. any demerit the recording officer may impose can't exceed that required for suspension, after which a reset takes effect. To implement this idea two columns are required. One for the imposed demerit points and another for those that become effective.

In the revised attached workbook the formula below is in D2 and copied down from there to the other rows.


The formula limits the number of effective demerit points to a quantity not exceeding 3, minus the modulus of previsously imposed effective points. Observe that the row number of the end cell in range $B$1:$B1 is not absolute. The range will expand to $B$1:$B2 in row 3. Further note that the range starts in the row above the first data row (which is row 2) because the previous quantity must be extracted (the current one ignored). In the attached workbook referencing row 1 doesn't matter because no valid ID will be found there and nothing will be counted. Strictly speaking, this isn't good however. If you wish to avoid it you may have to treat row 2 different from the others.

Entries like "1 point" or "2 points" are not good. They give you all kinds of trouble because Excel is about numbers, not text. In columns C:D the text is inserted by number format. Select C2:D30, right-click -> Format Cells -> Number -> Custom and paste the formula below in the Type field.

[=1]0 "point";0 "points"

The formula instructs, "If the cell value = 1 then add " point" to the number, else add " points". Note that the cell value is the number, like 1. The number is displayed as "1 point". The cell value is not "1 point". In consequence you can use the cell for calculations and don't need COUNTIFS but SUMIF, which makes your life a lot easier.

The whole thing is a bit tricky. It's entirely possible that (a) you may not be able to integrate the solution into your project as it stands now or (b) there is an error in it which escaped my attention. I think the key point is how to set the variable ranges for the SUMIF functions. If you can't move your project to use SUMIF the same method can be applied with COUNTIFS, and if my apologies for possible errors are insufficient I shall be glad to help you iron them out.

In short, let me know if you need more help.



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.
Lightkeeper (rep: 2) Dec 25, '18 at 5:17 am
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 (rep: 2) Dec 25, '18 at 5:17 am
I'm afraid, I'm not much smarter than before. It seems that the officer can give 1, 2 or 3 points. I presume that 3 points (suspension) triggers legal action. Hence the point system is reset immediately when this limit is reached. The MOD formula I suggested should work fine. Therefore 2 questions. (1) Do you have Excel? Have you seen my workbook? (2) What does the result you want signify? I understand it to be the current point number. I don't understand why my suggestion shouldn't work.
Variatus (rep: 3388) Dec 25, '18 at 7:28 pm
Since you are moving data from an initial sheet to a more data-friendly one, why don't you convert the "1 klip" to a system like I explained at that point? If the officer can give 1, 2 or 3 points the rule should also be that he can't give more than to a total of 3. So, if the offender already has 2 points he should give 1.  As an alternative, if the officer doesn't know how many points the offender already has he would give 3 for the same offence to everyone and the system would reduce the total to "suspension" at some other point. Somehow this seems to point to the question of what the formula you want is for. It seems that I can't imagine any purpose other than extract the current status. However, I also seem to have insufficient understanding of the data capture. I am looking for a database, actually, precisely like the one you copied above but with "3" in place of "suspension". It seems I am arguing about how to portray the data. If it were 1,2,3 my formula would get the status correctly, If it needs to be "1 klip", "2 klip" and "Suspension" my formula would need to convert the text to numbers and then still process them with the same MOD function.
Variatus (rep: 3388) Dec 25, '18 at 7:42 pm
I get what you're saying now. After I wrapped my head around it. As far as points it works very well. I added the following to a new column:

=IF(V117="";"";IF(V117="Suspension";3;IF(V117="1 point";1;IF(V117="2 points";2))))

And then I used the following code:


It works very well, and I'm very close to accepting your answer. 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 edit the code so the Suspension (3 points whatever you call it) always resets to 0? Then I will happily accept your answer.
Lightkeeper (rep: 2) Dec 26, '18 at 12:40 am
I'm sorry, I need some help implementing it. I have two columns now:

Column W:
=IF(V117="";"";IF(V117="Suspension";3;IF(V117="1 point";1;IF(V117="2 points";2))))

Column X:

But then I need to lookup the social security number from H3 and return the number in a cell. In this case it's U5. D117:D10000 has the social security to lookup. Now what to write in U5?
Lightkeeper (rep: 2) Dec 26, '18 at 8:59 am
Your formula in clm W is flawed because it has no FALSE result for the last, nested IF statement. Try this formula instead. =IFERROR(INDEX({1,2,3},1,MATCH($V117,{"1 point","2 points","Suspension"},0)),"").
SUMIF($D$117:$D117;$X118;$X$117:$X117) looks for X118 in D117:Down. Please change this clm to the one with SS numbers. $X$117:$X117 correctly specifies the  effective demerits prior to row 118. Row 118 must be the first SS number. Row 117 must be clm caption or blank. The formula must be in row 118.
H3 should hold an SS number which exists in clm X. U5 should show the current status of demerit points. That is the simple modulus of the SUMIF in column X, like =MOD(SUMIF(YY118:YY10000,H3,X118:X10000),3), where clm YY is the column with the SS numbers. No absolute referencing required because the formula is never copied anywhere else.
Variatus (rep: 3388) Dec 26, '18 at 8:22 pm
Be careful with row 117 (or perhaps 116). In clm X the SUMIF lloks for demerits prior to the current row, meaning excluding the current row. Where the "current" row is the first row, "prior" means before time was counted, meaning outside the table. If row 117 has the first data, "outside the table" is in row 116. Make sure that no match of H3 or X118 can ever be found there. The row must be included so that all formulas can be the same but it must never return a result. So long as it never holds a SS number (and, preferrably, is not blank - such as a column caption) that condition will be met.
BTW, your SS/Demerit table belongs on a dedicated, separate worksheet. It also belongs in a table (ListObject). I would prefer it to be both, in a table on a dedicated sheet. I presume that you didn't do that in order to simplify addressing. Now, that would be a rewarding question for me to answer :-)
Variatus (rep: 3388) Dec 26, '18 at 8:31 pm
I have made a copy for you. Maybe you can try in a live document? Some stuff is not working because I exported a Google Sheet document to Excel. Really sorry, but I cannot wrap my head around this - and I don't want to waste your time anymore forcing you to write so much explanation and I still can't get it to work.

Sorry, it's in Danish. But the columns is as I said. Thanks.

(removed link)

My Discord is Lightkeeper/Jake#8025 if that's easier. Let me know if you prefer an other comunication form.
Lightkeeper (rep: 2) Dec 26, '18 at 9:00 pm
The normal way would be to attach the workbook to the question or answer. I don't want to do the latter because I'm not sure how private it is.
1. Your DB starts at row 117. The row above it is row 116 which is hidden. That represents no hurdle per se but some of its values have #REF errors. They should be cleared as a matter of principle. D116 and X116 are accessed by my formulas. I prefer them not to be blank (X116 is blank). This is a precaution: You should know what is there. D116 must not contain a SS number. X116 must not contain any number. Wrong results may occur only when both faults come together or the value in D116 is entered in H3.
2. I prefer =IF('Formularsvar 1'!$V:$V="","",'Formularsvar 1'!$V:$V) in column V. Enter as array formula in V117 if you agree. The difference is that your formula returns 0 and my suggestion returns "". One is a number, the other text. Text can't be used in calculations: makes a difference when referencing (see below).
3. Your formula inW117 is wrong. This is the corrected version. =IF(V117="","",IF(V117="Ubetinget frakendelse",3,IF(V117="1 klip",1,IF(V117="2 klip",2,"")))). The difference is here: IF(V117="2 klip",2,""). If none of the 3 choices are True then "" results. Your formula returns "False" because "" is missing.
Variatus (rep: 3388) Dec 27, '18 at 4:31 am
4. I prefer this formula in W117 =IFERROR(INDEX({1,2,3},1,MATCH($V117,{"1 klip","2 klip","Ubetinget frakendelse"},0)),""). It does the same job but it will not confuse you (as the nested IF obviously did), once you have understood it.
5. The correct formula in X117 is =IF($W117="","",MIN(3-MOD(SUMIF($D$116:$D116,$D117,$X$116:$X116),3),SUM($W117))). It is different from what I posted earlier in IF($W117="","", and SUM($W117) where I had just $W117 before. The SUM function evaluates "" to 0. The simple $W117 recognises "" as text and balks.
6. Copy all columns down as required.
7. U5 is OK. It should now return the correct result. You should set up some tests to prove it. That is time consuming but the only way to gather confidence :-)
Variatus (rep: 3388) Dec 27, '18 at 4:31 am
It works amazingly! Thank you so much! I really appreciate all your help and I really couldn't have done it without you! I hope you celebrate the new year in the best possible way and wish you a happy new year. I can't put into words how much this means to me. I wish there were more people like you. I will now accept your answer with pride :D Thank you, thank you, thank you!
Lightkeeper (rep: 2) Dec 27, '18 at 5:06 am
Wow! I'm glad I could and did help out. Happy News Year! 
Variatus (rep: 3388) Dec 27, '18 at 7:35 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login