On my spreadsheet I have a list of 20 items. Each item is assigned a number, and I use a random number generator to randomly select one item from the list of 20 and display it in an adjacent cell.

I would like to have an additional number next to each item on the list which increases by 1 every time that item is selected. How can I use formulas to count the iterations of each item on the list?

  • What do you mean by "a random number generator"? Are you using a built-in function, an addon or a script? Did you already search for a similar question on this site? Reference How to Ask. Nov 21, 2016 at 18:26
  • I did look on the site for similar questions but didn't find anything that quite fit.I use the function RANDBETWEEN to generate the numbers Nov 25, 2016 at 7:16

1 Answer 1


Possible but in general inadvisable to do so with formulae only. Say your list is in A1 to A20 and the selection is made in C1.

In File > Spreadsheet settings... > Calculation set Recalculation to On change and turn On iterative calculation with Max. number of iterations set to 1.

Temporarily disable the formula in C1 and in B1 enter:


and extend the series down to suit, then re-enable the formula in C1.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.