formulasgoogle sheets
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?
You could use a simple formula to build a string defining the Range, name that location, and then access it with the INDIRECT Function in your formulas. E.g. for your question above.
Put in any cell (e.g. B3) a formula like:
= "Sheet!$A$1:$A$" & B2
Name Cell B3 as "items" as a named range.
Then whenever you would have used "items" in a formula just use
INDIRECT(items)
It is not quite the same as a dynamic named range, but almost as neat and certainly as effective.
In Google Spreadsheets, I would do it a bit different.
=COUNTA(SPLIT(A1, " "))
The SPLIT function is only available in Google Spreadsheet and will split the cell's content on every space (" "). The COUNT function will simply count the instances.
The Excel formula gives the same answer, but a bit more laborious:
=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1
See following answer on which formulae are unique to Google Spreadsheet:
https://webapps.stackexchange.com/a/44719/29140
Best Answer
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.