AND-OR-COUNTIF Question

Szilvia

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

Paul Newcome

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)

Paul Newcome

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)

Szilvia

Thanks so much, Paul for the suggestion.

It worked perfectly!

Paul Newcome

Happy to help. 👍️