AND-OR-COUNTIF Question
Hi,
I started to build a formula and it doesn't return the required values.
I have a sheet for distribution board testing. There are 25 yellow checkbox columns and 24 green checkbox columns. I have a column with 4 colour symbol (L1-Quad), and a checkbox column (All DB Tested) where the formula needs to return the value.
My formula is as the below
=AND(OR(AND(COUNTIFS([Yellow DB-01-39B/ 4L1]@row:[Yellow DB-WG-01-03/ 3L2]@row, 1) = 25, 1, [L1 - Quad]@row = "Yellow", AND(COUNTIFS([Green DB-01-54B / 4L2 ]@row:[Green DB-ND-01-01/ 1L2]@row, 1) = 24, 1, [L1 - Quad]@row = "Green"))))
My aim is to return (All DB Tested) column checked if yellow is selected and all yellow columns checked or if green selected and all green columns checked.
Please could somebody have a look and tell me know how to change the formula?
Thanks
Szilvia
Best Answer
-
My suggestion would be a helper column on the source sheet that puts the number 1 on each row that meets your criteria. Then you can simply SUM this helper column.
=IF(OR(AND([L1 - Quad]@row = "Yellow", COUNTIFS([1st Yellow Check]@row:[Last Yellow Check]@row, @cell = "") = 0), AND([L1 - Quad]@row = "Green", COUNTIFS([1st Green Check]@row:[Last Green Check]@row, @cell = "") = 0)), 1)
Answers
-
My suggestion would be a helper column on the source sheet that puts the number 1 on each row that meets your criteria. Then you can simply SUM this helper column.
=IF(OR(AND([L1 - Quad]@row = "Yellow", COUNTIFS([1st Yellow Check]@row:[Last Yellow Check]@row, @cell = "") = 0), AND([L1 - Quad]@row = "Green", COUNTIFS([1st Green Check]@row:[Last Green Check]@row, @cell = "") = 0)), 1)
-
Thanks so much, Paul for the suggestion.
It worked perfectly!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!