Return "True" when row 2 and the corresponding row beneath both contain "yes"

[Ross321]

Hi, I'd appreciate any help developing the shortest formula reasonably possible.

Function is to return "True", when row 2 and the corresponding row beneath both contain "yes"

Thanks in advance


2020-03-31 11_04_46-Book1.xlsx - Excel.png

[Richard Buttrey]

*****Late edit. G2:X2 should have been Absolute.i.e. $G$2:$X$2


Hi and welcome to the forum.

We generally prefer to see the workbook, if only to test a solution, so please upload the workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

In addition please clarify whether all relevant columns must be Yes or is it sufficient that one of them has a Yes for any particular row.

Untested but maybe in B4 as an array formula entered with Ctrl-Shift-Enter and Copied down

Please Login or Register  to view this content.

adjust the column X as necessary

[Ross321]

Thought I did include an upload. Sorry, newbee. Trying again

[ChemistB]

So everytime a Yes appears in row 2, there should be a corresponding Yes in the same column in the corresponding row? I don't see that for row 7 so B7 should be false?

[Ross321]

Hi ChemistB,

A "yes" in the first location does not mandate a "yes" in the second location. The two locations are independently variable. The "True" result is only generated when BOTH locations contain "yes".

[XOR LX]

Hi,

In B4:

=COUNTIFS(G$2:IR$2,"Yes",G4:IR4,"Yes")>0

and copied down.

Regards

[Ross321]

Thanks XOR LX! This works great, and is much shorter than the way I was going at it. Very helpful!

Ross

[XOR LX]

You're welcome!

Cheers!

[Richard Buttrey]

Just a quick point.

XOR's formula is counting the number of Yess in row 2 and the relevant row. As far as I can see it won't take into account the last condition in your #5 which I assume to mean that there must be a Yes in the same column on each row for each Yes column in Row 2.

Or maybe I'm missing something.