I am trying to assign multiple tokens to individual data entries using a single cell per entry for tokens. I am currently using countif as noted here:
=COUNTIF(A:Z, "tokenname 1")
=COUNTIF(A:Z, "tokenname 2")
=COUNTIF(A:Z, "tokenname 3")
This only lets me put one token per cell, which would be space prohibitive as this sheet needs to be human/screen readable. I’d like to just be able to detect tokens anywhere in the entire sheet and count them at the end. The tokens are unique strings of letters, not numbers, and I can’t use numbers as tokens and disambiguate at the output. There are going to be up to 720 entries on this sheet, so making the whole thing prohibitively tall or wide would impede readability.
Frommerman is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
Counting Different Values inside a Sheet
For starters since you are using the value A:Z, then that means that the whole sheet is filled with data, and you are using the formula to another sheet.
There are multiple interpretations with your question you can try 2 of the approach or recommendation I can provide based on your question and current formula.
Recommendation
If you are just looking to get the final number then you can use this:
=SUM(COUNTIF('100_random_fruits'!A:Z, "Fig"),COUNTIF('100_random_fruits'!A:Z, "Lemon"),COUNTIF('100_random_fruits'!A:Z, "Blueberry"))
If you are looking to get a vertical stack of numbers per each token you can use this you can also use VSTACK Function for this:
={COUNTIF('100_random_fruits'!A:Z, "Fig"),COUNTIF('100_random_fruits'!A:Z, "Lemon"),COUNTIF('100_random_fruits'!A:Z, "Blueberry")}
Sample Output for both respectively:
| Sample Output for Sum of All | ||
|---|---|---|
| 367 | ||
| Sample Output for Stacking | ||
| 126 | 241 | 0 |