Using MacOS Numbers to count the number of cells containing hello or world:

. Y
1 hello
2 world
3 hello world
4 world world
5 world hello
6 world bye
7 hello flynt

The result I'm expecting is 7.

I'm lost trying to figure out formulas :

COUNTIF(A1:A7;"hello")+COUNTIF(A1:A7;"world") will return 9.

COUNTIFS(A1:A7;"hello";A1:A7;"world") will return 2

Is there a simple way to do this like in unix

grep -e hello -e world /tmp/test
3|hello world
4|world world
5|world hello
6|world bye
7|hello flynt

grep -e hello -e world /tmp/test | wc -l


1 Answer 1


You actually have the answer there. Take the difference of the two formulae.

The logic is count all those having Hello + all haveing world - those having both so

COUNTIF(A1:A7;"hello")+COUNTIF(A1:A7;"world") - COUNTIFS(A1:A7;"hello";A1:A7;"world") 

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .