Brilliant!

Many (many many) thanks

Keith

"Luke M" wrote:

I believe the issue is how the AND function handles arrays. It doesn't
properly multiply the two arrays to create a single array, and thus "clunks
out". Workaround is to manually force the arrays to multiply:

=IF((LEFT('Raw report'!$1:$1,4)=V$1)*('Raw report'!2:2=1),"z","x")

Now the formula is forced to create a single array composed of 1's and 0's
before moving on to the true_response and false_response portions of the IF
function.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"ker_01" wrote:


I have a raw data worksheet that has similar column headers (in row 1)
identified by their first few letters; "B01_Yes", "B01_No", "B02_Yes",
"B02_No", etc.

Each row represents a single person's response to the survey. Their answer
to any question (B01, B02, etc) will show up in those cells as a 1 under the
corresponding answer column.

I'm creating a summary sheet that will just have a single column for each
question, and I want to populate the target cell with the actual answer
given. (Column headers= "B01_", "B02_", etc)

This formula correctly tells me if I the person answered just yes or no (and
not both):
=COUNT(AND(LEFT('Raw report'!1:1,4)=V$1,'Raw report'!2:2=1))
and I've confirmed that it returns a value of 1
additional info:
'Raw report'!1:1 are the headers, so taking the left 4 digits gives me the
matches of B01_(Yes) and B01_(No) when V$1 = "B01_"

However, when I wrap it with an IF statement, it returns an answer of FALSE,
in this case, returning the "x" value/
=IF(AND(LEFT('Raw report'!$1:$1,4)=V$1,'Raw report'!2:2=1),"z","x")

I'm thinking that COUNT ignores possible text or date values in my data row,
and therefore can return a simple integer, but that the IF statement maybe
can't get past non-numeric values when trying to calculate ['Raw
report'!2:2=1]

What would be a better way to evaluate whether the data row contains a 1,
that wouldn't be affected by some cells having non-numeric values?

Thank you!!
Keith