IF formula that looks at a range of cells

[Olly9]

Hi,

I'm trying to create a formula that will give me a true/false outcome based on looking at data within 2 columns. I have tried an IF AND formula, but this only seems to work if I am referencing the specific cell, but I actually want it to look at the whole column.

As an example of what I'm trying to achieve: If Column A:A has an "X" anywhere down it, then reference column C:C and look for "Y" on that row then return true, if not return false.

I tried =IF(AND(A:A="X",C:C="Y"),TRUE,FALSE) but this didn't work, but does if you specify the cell with the X, however the X will move manually each month so I want it too look at the whole column if possible.

Can anyone help please?

[jason.b75]

Try

=COUNTIFS(A:A,"X",C:C,"Y")>0

[Olly9]

Thank you jason.b75, that works perfectly.

[Olly9]

Is there a way to also get this formula to work if one of multiple criteria are true?

So taking the =COUNTIFS(A:A,"X",C:C,"Y")>0 but also come back true if C:C has either "Y" or "Z" for example?

Thanks.

[Pete_UK]

Try it like this:

=SUM(COUNTIFS(A:A,"X",C:C,{"Y","Z"}))>0

Hope this helps.

Pete

[jtakw]

NVM, already answered.

[Olly9]

Try it like this:

=SUM(COUNTIFS(A:A,"X",C:C,{"Y","Z"}))>0

Hope this helps.

Pete

Thank you so much Pete. I was really close with getting this one right, but for some reason couldn't get it to work!