Good evening everyone
I was wonder if anyone can help me with this formula
=COUNTIF(A:A,A3)
In colum A I have a list of Ids that often repeat so I used that formula to count them. Now what I am wanting to do is make it so after the Id has been counted up to 4 it resets back to 0 and starts counting again..
What would I need to add to that formula to make it count all the repeated Ids to 4 then reset to 0 and start counting again I am new to excel..
Hi and welcome
although we value privacy as much as anyone else, it could be important that members have a rough idea of your location. You might in the future post questions which are bound to your regional settings.
So, please update your profile to something more precise then "Uknown" ( country will suffice, no need to be more precise).
Thank you for helping us to help you
I updated the info
Thanks a lot
Your explanation is not clear. Take the name QQQ. First time in list =1 then 2nd, 2; 3rd, 3; 4th, 4; 5th zero (as you stated) or 1 again???
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
If you REALLY meant start again at zero;
=MOD(COUNTIF($A$2:A2,$A2),5)
and if you REALLY mrant start again at 1:
=1+MOD(COUNTIF($A$2:A2,$A2)-1,4)
Last edited by Glenn Kennedy; 09-20-2019 at 02:51 AM.
Re: Count upward then once certain number reached reset back to 0
=COUNTIF(A:A,A3)
I drag down the formula so it applies to all colums in colum A..
I am gonna try a few of the reccomended formulas
Last edited by aleross; 09-20-2019 at 02:59 AM.
It worked your formulat Thank you very much.. for your help
your solution worked..Originally Posted by Glenn Kennedy
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
Glenn Kennedy I like your formula solution I have one last question is it possible to make it say a word like FREE after counting to 4 For example 1,2,3,4,Free as the last entry before it starts to count again?
Both your examples worked =1+MOD(COUNTIF($A$2:A2,$A2)-1,4) and =MOD(COUNTIF($A$2:A2,$A2),5) I just was wondering if its possible to replace the 0 with the word Free then it starts counting back up again?
Yes. In B2, copied down:
=IFERROR(1/(1/MOD(COUNTIF($A$2:A2,$A2),5)),"Free")
Last question I copied it down your formula It works but It shows "Free" in all the colums even the ones that dont have data and I just want it to show "Free" when its the 4th one.
Yep:
=IF(A2="","",IFERROR(1/(1/MOD(COUNTIF($A$2:A2,$A2),5)),"Free"))
Thank you works man your fast..
Have to... most of my work has been involved in responses to a certain type of emergency!!
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks