Calculate number of times a specific set of characters (such as "word" or "mood") are present- exclusively- in individual cells within a defined selection of cells?

[appreciatethehelp]

Mac Mojave, Libreoffice 6

I want to quantify the number of times cells containing, exclusively, specific sets of characters appear in a selection of cells within a Calc spreadsheet, then display this quantity in a cell.

For example say I have a 3-column, single-row spreadsheet, with the characters “calculate” present in the first cell, and “calculate calculate” in the second; I want to display the number of times the word “calculate” and only that specific set of characters appeared in those two cells in the third cell- in this case the number 1 should be displayed, acknowledging the only valid appearance of the word/characters (the first cell, as the second was invalid due to containing more characters than specifically “calculate”.

Can I use an IF formula variation, or should I try something else?

Thanks.

[mikekaganski]

Given lack of information about what “specified text” might be, and what “appears in a given area” might mean, I assume that the text might e.g. appear several times in a cell in the area, which should be counted several times…

then if the given area is in column A, and specified text in B1:

=SUMPRODUCT((LEN(A:A)-LEN(REGEX(A:A;"\Q"&B1&"\E";"";"g")))/LEN(B1))

[mariosv]

Hi @Mike2 use sumproduct with column reference ‘A’ can make the spreadsheet very slow, because it evalueates the whole column not only up to the las row with data like other functions seems to do.

[mikekaganski]

@mariosv: of course - the real point was “too little information is known, and thus too broad assumptions had to be made”.

[appreciatethehelp]

@mikekaganski Have added detail to original question, including an example. Apologies.

[mikekaganski]

@appreciatethehelp: your clarification didn’t clarify:

1. If the word “calculate” will be the whole content of the area cells;
2. If it might be present several times per cell;
3. How it should be counted if it might appear more than once per cell: e.g., “count every appearance of the word, e.g. if a word appears three times per cell, 3 should be added for this cell”, or “if the word appeared in a cell, no matter how many times, we should add 1 to the result”.

[appreciatethehelp]

1, 2 & 3. I only want to count cells with the particular phrase exclusively - only cells with the exact characters ‘calculate’ and nothing else (including multiple iterations of the word) displayed, at 1 per cell

If you care to it would interest me (but is not strictly necessary for the purposes of this question) to see the difference between this and another scenario: the word “calculate” not needing to be the whole content of area cells i.e. still being counted if coexisting with other characters within cells (however still needing to be separated by a space- ‘calculate’ in “I’llcalculatetime” shouldn’t be counted, but should be in “I’ll calculate time”; being counted at a value of 1 for every appearance of the word, regardless of whether it appears multiple times, or only once, within a given cell, therefore if the area of cells in question consists of two cells containing “I’llcalculatetime calculate calculate” and “calculate” respectively, the number 3 should be displayed