Ask Your Question

Revision history [back]

click to hide/show revision 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.

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.

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.