Count if all, cells contain a specific word.

Britt E

Hello,

I am still new to using formulas and I am hoping someone can help with finishing this one. I have an information sheet and a metrics sheet to build a dashboard off.

On the dashboard metric sheet, I am wanting to count the cells in a column ion the data sheet that contain the text "PPM" the column I am referring to is a dropdown and allows multi. Currently I have

=COUNTIF({Range 1}, [Column1]@row)

(Row cell contains the word I am searching for.)

That currently returns count for cells that only contain the @cell, not cells with multi values within them.

What is the extra part to the formula I would need? please help?

Thank you

Itai Perez

Hey @Britt E

Try this formula: =COUNTIF({Range 1}, CONTAINS("PPM", @cell))

It worked fine for me but let me know if I missed anything.

Nick Korna

Hi @Britt E,

For this use a cross sheet reference to your dropdown column only and a formula like this:

=COUNTIF({Dropdown}, CONTAINS("PPM", @cell))

Hope this helps, but if you've any problems/questions then just ask! 🙂

Britt E

Thank you both @Nick Korna and @Itai , that works well.

What could I do if I want the count to be dependant on cell value that is in another column that is just free text determined by a formula?

Nick Korna

You'd change the formula to COUNTIFS and add in the extra column(s) & criteria. That the other column's value is calculated rather than directly input shouldn't matter.

If you're looking for a single text value, then you can just use "text" as the criteria, or use CONTAINS in a similar way.