I wrote a formula to test the input in 5 cells within a row. That works great, but when I change the formula to an array formula to test the input of 20 rows at the same time, the array formula doesn't work.
Here are the details:
I have a range with 5 columns and 20 rows. The columns are A thru E...
Within each row of the range, the user can enter >Update:
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
= = = = = = WHERE I ENDED UP = = = = = = = = = = = = = = = = = = = = = = = =
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Thanks for the help. What I was trying to do ended up being bigger than a breadbox. . .
My problem – look thru an R x 5 matrix and see if any row is not "OK."
These are OK (correction made, the above was wrong):
0 0 0 0 0
1 1 1 1 1
0 1 1 1 1
1 1 1 0 1
Any other combo is not OK.
In the end, I had to create a user defined function instead of doing some slick arithmetic:
For all rows in a R x 5 matrix. . .
If sum of all 5 entries = 0 or 5, then OK (check next row)
if first entry is 0, then if sum of last 4 entries is 4, then OK (check next row)
if first entry is 1, then if the sum of the second, third and last entries is 3, then OK (check next row)
else Return "Not OK!" (end)
Copyright © 2024 Q2A.ES - All rights reserved.
Answers & Comments
Verified answer
UPDATED
based on your first description I didn't understand that 0 1 1 1 1 was OK
here is a redone formula that will count the number of rows that match the 4 options you listed in your Addition Details section
=SUMPRODUCT((A1:A20=0)* (B1:B20=0)* (C1:C20=0)* (D1:D20=0)* (E1:E20=0)) +SUMPRODUCT((B1:B20=1)* (C1:C20=1)* (D1:D20=1)* (E1:E20=1)) +SUMPRODUCT((A1:A20=1) *(B1:B20=1) *(C1:C20=1) *(D1:D20=0) *(E1:E20=1))
hope that helps