Is there a function to count the number of times certain phrases exist in the file?

Hi Phongsan,

COUNTIF is your friend here:

Formula in C2, and filled down for as many rows as needed:

C2: COUNTIF(B,B2)

Note that for space reasons here I have hidden rows 14-25. The cells in column B contain repeated copies of the four example numbers, with the final row containing the 8th example of the number in B2.

Regards,

Barry

Hi Phongsan,

No mention of 'multiple sheets' in the question might explain that detail not being considered in the response. 😉

UNION.RANGES may be useful here.

I duplicated the table above three times to create a set of four, then renamed them T-1, T-2, T-3 and T-4 to keep the formula short and readable. If the Tables are given distinct names, each name will be sufficient to identify that table in a formula, and you will not need to include the Sheet name(s) of the Sheet(s) containing each table.

Here is the revised formula, as it appears in cell C2 of the first table, shown in my earlier reply:

Notice that T-2, T-3 and T-4 are named in the formula, but T-1, the table containing this copy of the formula is not named in either the blue token (naming the first range contained in UNION.RANGES) or the green token (naming the cell containing the value to be counted by COUNTIFS.

When this cell (C2) is selected (single click) and copied (command-C), then pasted into T-4::C2, this is how it appears:

Notice that the formula here contains three tokens in which the table is not named. The first blue token, which should be pointing to T-1::B is now pointing to column B on 'this table'. Edit this token to read T-1::B.

The second blue token, which displayed T-4::B above, is now in a copy of the formula on T-4, and as T-4 is 'this table', the table name is not needed. (Colours will differ, but but this edit will be needed for one reference EACH time the formula is pasted to a new table.

The third token, showing B2 is correct, as it always refers to the cell on 'this row' of column B on 'this table', containing the value to be counted. The number 2 will change to match the row containing the formula as it is filled down column C on each table.

For the sample table plus its three duplicates, the totals should now be four times what they were in the single copy of this table: 32 for the first value, 28 for the others.

Regards,

Barry

Hi Barry,

Neat solution.

Phongsan wrote in the original question:

counts the number of times the serial number in the same row exists in the entire file that have multiple sheets.

Barry wrote:

No mention of 'multiple sheets' in the question might explain that detail not being considered in the response.

Just sayin' 👿

Regards,

Ian.

Phongsan wrote:

Obviously, it's very tedious, and I can't do all the thousand rows of these manually. Is there a function that can help me do that? Then I could just copy the function to the next rows to complete the task.

Hi Phongsan,

Have you considered rearranging your data so that you don't have to go through a tedious procedure?

Generally it is much more efficient to have "like" data together in one table rather than scattered across multiple tables on multiple sheets. Then you can easily use Filters and SUMIFS, COUNTIFS, etc. to view subsets and extract counts and sums.

Note also that Numbers isn't really designed to handle large data sets. If you are dealing with many thousands of rows you find performance becomes sluggish.

SG

Phongsan wrote:

the data are on separate files and I had to merge them in one file but on separate Sheets

But if it's the same kind of data, why separate sheets? For the same kind of data one table on one sheet can be more efficient.

SG

Thanks, Ian!

I must have been reading the original message with my eyes closed. 😊

Apologies to Phongsan. 🙂

Barry