Since it is related, I'll post my followup here (hoping that it is noticed
even though I've closed the thread).

It looks like I spoke too soon; after additional testing, I narrowed the
formula down to:
=IF((LEFT('Raw report'!$A1:$IH1,4)=V$1),"z","x")

which eliminates the COUNT and AND statements. It also eliminates the
'unlimited' range of the full row (1:1) in case that was a problem.

It still returns "x" (False) even though I have confirmed that there is one
(and only one) match using
V4: =Left('Raw report'!P1,4) = gives the expected value
V5: =V4=V1 = evaluates to 1 (true)
Autofilling the formula left and right shows that all other columns return 0
(false)

Once I get this working, it will be embedded in a statement that includes a
sumproduct (part of an outer IF statement) so I don't think I can use an
array formula, although it could be that my syntax attempts are just wrong

Any additional assistance would be greatly appreciated!

Best,
Keith



"ker_01" wrote:

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