Countifs on a column that is a multi-select drop down..
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)
Best Answer
-
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.
Answers
-
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))
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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 🤔
-
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?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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.
-
Amazing - thanks guys!
I simplified the formula and added in @CELL (What does this actually do?) and seems to work now!!
-
The "@cell" basically tells the formula to evaluate the previously established range on a cell by cell basis.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!