google sheets
I'm having the following set of data:
A simple COUNTA would yield n=4. Using the above-mentioned formula will give also n=4.
The answer needs to be however n=6: Jacob Jan (1x), Jacob (2x), Jan (3x)
How can I count the number of occurrences of each word in column A?
I was able to create two options:
=COUNTUNIQUE(FILTER(A2:A11,E2:E11<>""))
or
=COUNTA(UNIQUE(FILTER(A2:A11,E2:E11<>"")))
=COUNTA(QUERY(A2:E11, "SELECT COUNT(E) WHERE E<>'' GROUP BY A label COUNT(E) '' "))
The first two formulas are identical. They filter column A, where column E has entries. Afterwhich column A is filtered for unique names. These names are than counted. The COUNTUNIQUE function combines the COUNTA and UNIQUE.
The second formula will count the number of occurrences per unique business name having an entry (<> '' and group by). The result is counted. Remove the COUNTA function to see the intermediate result.
See you're own example file for the results !!
Use the following formula to accomplish that.
=QUERY(
'GR Live Campaign Tracker'!J:L, // data range
"SELECT J, COUNT(L) WHERE L IS NOT NULL GROUP BY J", // select
0 // headers
)
Added the formula into your example file.
Best Answer
Hm, that's an interesting question. If you're looking for just the n of terms in the array, you can do this without splitting the terms.
Alright, here's what's happening in this formula.
len()of the string and subtract thelen()of the string after substituting the comma. In this case we get9 - 9 = 0. We then add1to get1(the number of terms in the cell)arrayformula()on this to apply the same subtraction function to every cell in the given range (in this caseA1:A4)sum()the numbers from the resulting range and get the total # of terms that are split on the comma.Let's look at cell A2 to see a case of when the comma is used. We take
len(A2)which is 10 and subtractlen(A2)after substituting the comma for a null character "" which is 9. We get10 - 9 = 1then we add1to the total to get2which is equal to the number of terms in the cell.Hope this helps!