Countifs on a column that is a multi-select drop down..

JTowler

I am trying to do a countif and one of the fields is a multi-select drop down and isn't returning the correct value, no idea what to do

R4+ BP007 Change Inventory Range 1 is the field that is the multi-select column.

=COUNTIFS({Status}, =[01 Open]$1, {Primary Function}, =$[Primary Column]5, {Fit/Gap}, ="Gap (Core Process)") + COUNTIFS({Status}, =[01 Open]$1, {Primary Function}, =$[Primary Column]5, {Fit/Gap}, ="Gap (Local Process)") + COUNTIFS({Status}, =[01 Open]$1, {Primary Function}, =$[Primary Column]5, {Fit/Gap}, ="Gap (Non-functional)", {R4+ BP007 Change Inventory Range 1}, $[01 Open]$10)

Paul Newcome

I personally am most curious about that third range - {Fit/Gap}

It looks like that is the only thing changing across each of the different COUNTIFS aside from the last one containing {Range 1} and the HAS function.

You also need to use "@cell" inside of the HAS function instead of duplicating the range.

Paul Newcome

Try this:

=COUNTIFS({Status}, =[01 Open]$1, {Primary Function}, =$[Primary Column]5, {Fit/Gap}, ="Gap (Core Process)") + COUNTIFS({Status}, =[01 Open]$1, {Primary Function}, =$[Primary Column]5, {Fit/Gap}, ="Gap (Local Process)") + COUNTIFS({Status}, =[01 Open]$1, {Primary Function}, =$[Primary Column]5, {Fit/Gap}, ="Gap (Non-functional)", {R4+ BP007 Change Inventory Range 1}, HAS(@cell, $[01 Open]$10))

Genevieve P.

Hi @JTowler

I see you marked Paul's answer as not resolving your question. Can you clarify what happens when you add in the HAS function with @cell, do you receive an error or an incorrect result? If you receive an error, which one is it? Can you post screen captures (but block out sensitive data)?

Cheers,

Genevieve

JTowler

Hi both,

Thanks for your responses - I've added in the formula as above but it's bringing back the incorrect count, this is the one formula I can't seem to get my head around - there aren't any errors. The total is coming back as 131 however the total should be 81 🤔

Genevieve P.

Hi @JTowler

Can you clarify what each of your four ranges are? It would be helpful to see a screen capture of each of these columns, noting what type of column they are:

{Status}

{Primary Function}

{Fit/Gap}

{R4+ BP007 Change Inventory Range 1}

Is it possible that one of your three statements is creating duplicate Counts?

Paul Newcome

I personally am most curious about that third range - {Fit/Gap}

It looks like that is the only thing changing across each of the different COUNTIFS aside from the last one containing {Range 1} and the HAS function.

You also need to use "@cell" inside of the HAS function instead of duplicating the range.

JTowler

Amazing - thanks guys!

I simplified the formula and added in @CELL (What does this actually do?) and seems to work now!!

Paul Newcome

The "@cell" basically tells the formula to evaluate the previously established range on a cell by cell basis.