How to count cells that contain specific text
✓ Verified in LibreOffice 25.8.7.3Count cells that include a word anywhere in them, not just cells that equal it exactly.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =COUNTIF(A2:A6,"*apple*") | The * wildcards match any characters before and after 'apple'. |
| Google Sheets | =COUNTIF(A2:A6,"*apple*") | Identical. |
| LibreOffice Calc | =COUNTIF(A2:A6,"*apple*") | Identical (wildcards enabled by default). |
How it works
Wrapping the search term in asterisks ("*apple*") makes COUNTIF match any cell that contains 'apple' anywhere. 'green apple', 'apple pie' and 'pineapple' all match, so the count is 3. Use "apple*" to match only cells that start with apple.
Verified, not just documented
We ran =COUNTIF(A2:A6,"*apple*") in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 3 — exactly the expected result. Every formula here is confirmed by actually executing it.