COUNTIF contains string in Google Spreadsheets

rubysue1
rubysue1 used Ask the Experts™
on
I am able to use
COUNTIF(A:A,"2 months")
to find all cells in column A with the value "2 months".
I would like, however, to be able to count all cells that contain the word "month" or "months". I've seen instructions for doing this in Excel with along the lines of "*month*" but I can't get that to work in Google Spreadsheets.  Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2015
Commented:
Use this formula.
=ArrayFormula(sum(if(isnumber(search("month",A1:A200,1)),1,0)))
Assuming you are checking a1 to a200. You can accordingly change this as well.
Saurabh
Top Expert 2015

Commented:
Or you can use this formula as well.
=ArrayFormula(sumproduct((isnumber(search("Month",A1:A3,1)))))
Upto you
Top Expert 2015

Commented:
Any particular reason for B Grade..?

Author

Commented:
This was such an excellent answer I just referred to it again. I have no idea why I gave you a B! Can I change that to an A?

Author

Commented:
Or just, in my case A:A seems to work.  Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial