I'm converting an Excel document. I used an array in Excel to count the number of times a certain word of text appeared in a group of cells. The array does not work in Numbers. How do I do this in Numbers?
iMac, OS X El Capitan (10.11.4)
Hi Bruce,
Using the SUBSTITUTE function is a common way to do this. Could you post your Excel formula?
SG
Hi Bruce,
COUNTIF should do the job for you. Here are two examples.
In the first, each cell in the group contains a single word.
Formula in B2 of the smaller table, and filled dow the column:
B2: =COUNTIF(Words :: $A:$C,A)
The second contains the same words, embedded in text strings of varying lengths. Although I've included a space before and after the word in each example, the space is not necessary to the operatin of the formula.
B2: =COUNTIF(Words in strings :: $A:$C,"*"&A&"*")
Fill down to end of column B.
The * is a wild card character meaning "any number of characters, including 'none'."
The total in the footer row at the end of each small table is a checksum to show me that all 27 words had been counted.
This method will count the number of cells containing the target word, but will not count multiple occurrences of the target word in a single cell. Cells containing more than one of the target words will be counted separately for each of the target words.
In the example below, where the entry in the single cell containing "avenue" has been edited to also contain "crescent" and "lane" (and to contain no spaces separating the words. Note the increases in counts for crescent and lane and in the total.
Regards,
Barry
Hi Bruce,
It would still be helpful if you could post your Excel array formula. 🙂 There may be a more direct equivalent.
SG
Just in case you are working on something that requires counting multiple occurrence of a target word in the same cell, one approach is like this:
The formula in B2, filled right and down, is:
=(LEN($A2)−LEN(SUBSTITUTE($A2,B$1,"")))÷LEN(B$1)
This takes the length of the original string, subtracts from it the length after all instances of the target word are removed, and divides that by the length of the target word. The result is the number of times the target word appeared in that cell. Do that for each cell and sum at the bottom.
If you don't have to many cells you are checking for counts then you can make the solution more compact with something like this in B2, filled right:
Chances are you won't get as compact as the Excel array formula, but would still love to see it.
SG
Here's what my Excel array formula is:
{=SUM(IF($D$5:$J$24=N3,1,0))}
In this cell N3 is "Bacon."
Then from the formula it looks as if you have single words in cells and Barry's COUNTIF approach applies. In your specific case I believe this is a direct equivalent:
=COUNTIF($D$5:$J$24,N3)
SG
Yes, the COUNTIF seems to be the solution. Thanks to you all. I'm working on the conversion now.
While you're reworking it, maybe change the "Excel-like" layout to something a little more "Numbers-like"–divide the big expanse of a sheet into discrete tables so you don't have to refer to a range out in the middle of a sea of cells like $D$5:$J$4.🙂
SG
SGIII wrote:
While you're reworking it, maybe change the "Excel-like" layout to something a little more "Numbers-like"–divide the big expanse of a sheet into discrete tables so you don't have to refer to a range out in the middle of a sea of cells like $D$5:$J$4.🙂
SG
That is my intention.
Count text instances in a group of cells?
