What formula do I use to count a specific text when multiple text values are in a cell?

Jessica_Sabin

I have a column with a drop down where you can select multiple options for one cell. It is tracking who is affected so it can list multiple departments. I want to track by department so I am trying to figure out how to count only a specific word in a cell when there are multiple valves in the cell. The formula I am using keeps giving me zero. What am I missing? Any help you can provide is greatly appreciated!! 😊

=COUNTIFS({ANC F&E Pre-OR Issue Entry & Tracking 2 Range 1}, AND(CONTAINS("WOA", {ANC F&E Pre-OR Issue Entry & Tracking 2 Range 1})))

Paul Newcome

You don't need the AND function, and the "range" in the CONTAINS should be "@cell". I also suggest using HAS when evaluating a multi-select type column.

=COUNTIFS({ANC F&E Pre-OR Issue Entry & Tracking 2 Range 1}, HAS(@cell, "WOA"))

Paul Newcome

You don't need the AND function, and the "range" in the CONTAINS should be "@cell". I also suggest using HAS when evaluating a multi-select type column.

=COUNTIFS({ANC F&E Pre-OR Issue Entry & Tracking 2 Range 1}, HAS(@cell, "WOA"))

Jessica_Sabin

@Paul Newcome Thank you. :) I am highlighting an entire column, not a cell which is why its listed like that. Is there something else I need to do for a column formula?

Paul Newcome

Right. The @cell tells the formula to run the HAS function on a cell by cell basis through the previously established range.

Jessica_Sabin

Thank you so much @Paul Newcome! That worked. You are a lifesaver. 😊

Paul Newcome

Happy to help. 👍️