First time here? Check out the FAQ!

1 | initial version |

**This is about counting in Calc.**

Assuming one of the "groups" mentioned in the original question is contained in A1, and in B1 a character, a word or a phrase to search for, then the formula

=SUMPRODUCT(IFERROR(MID(A1;ROW(OFFSET(INDIRECT("$A$1");0;0;LEN(A1);1));LEN(B1))=B1;0))

will return the number of occurrences. Overlapping occurences will be counted each. "Words" occurring inside words consisting of more characters will also be counted. Knowing nothing about Chinese I cannot tell if this will be acceptable. Using RegEx and a construct with SEARCH instead of the direct comparison of strings, additional control of the counting may be achieved.

The construct `OFFSET(INDIRECT("$A$1");0; ...`

is used to avoid errors caused by lost references after deletion of a row or a column in specific cases. Omitting this, `INDIRECT("$A$1")`

can be replaced by `$A$1`

.

The content of any cell is limited to a maximum of 65535 characters.

2 | No.2 Revision |

**This is about counting in Calc.**

Assuming one of the "groups" mentioned in the original question is contained in A1, and in B1 a character, a word or a phrase to search for, then the formula

~~=SUMPRODUCT(IFERROR(MID(A1;ROW(OFFSET(INDIRECT("$A$1");0;0;LEN(A1);1));LEN(B1))=B1;0)) ~~=SUMPRODUCT(MID(A1;ROW(OFFSET(INDIRECT("$A$1");0;0;LEN(A1);1));LEN(B1))=B1)

will return the number of occurrences. Overlapping occurences will be counted each. "Words" occurring inside words consisting of more characters will also be counted. Knowing nothing about Chinese I cannot tell if this will be acceptable. Using RegEx and a construct with SEARCH instead of the direct comparison of strings, additional control of the counting may be achieved.

The construct `OFFSET(INDIRECT("$A$1");0; ...`

is used to avoid errors caused by lost references after deletion of a row or a column in specific cases. Omitting this, `INDIRECT("$A$1")`

can be replaced by `$A$1`

.

The content of any cell is limited to a maximum of 65535 ~~characters. ~~characters.

3 | No.3 Revision |

**This is about counting in Calc.**

Assuming one of the "groups" mentioned in the original question is contained in A1, and in B1 a character, a word or a phrase to search for, then the formula

`=SUMPRODUCT(MID(A1;ROW(OFFSET(INDIRECT("$A$1");0;0;LEN(A1);1));LEN(B1))=B1)`

~~ =SUMPRODUCT(MID(A1;ROW(OFFSET(INDIRECT("$A$1");0;0;LEN(A1);1));LEN(B1))=B1) ~~

will return the number of occurrences. Overlapping occurences will be counted each. "Words" occurring inside words consisting of more characters will also be counted. Knowing nothing about Chinese I cannot tell if this will be acceptable. Using RegEx and a construct with SEARCH instead of the direct comparison of strings, additional control of the counting may be achieved.

The construct `OFFSET(INDIRECT("$A$1");0; ...`

is used to avoid errors caused by lost references after deletion of a row or a column in specific cases. Omitting this, `INDIRECT("$A$1")`

can be replaced by `$A$1`

.

The content of any cell is limited to a maximum of 65535 characters.

Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.