IF AND Covering Cell Ranges

[kradclayton]

Hi All

Looking for some help on a IF AND formula.

I have a spreadsheet with multiple tabs, I need for the formula to look at one tab range of cells and another tab range of cells and if the range of cells includes two true value, return a value.
I have been able to get this to work when im only looking at one cell per tab, but the data can sometimes appear in different cell columns (always on the same row however).

I need for the formula to be in the tab "Combinations" and to look at the "colour" & "room" tab. Essentially, if colour = Grey\/Black (from column B onwards) & room= Bedroom (from column B onwards) then return Bedroom/Grey in the combinations tab. I have this currently - =IF(AND(colour!B2="Grey\/Black",Room!B2="Bedroom"),"Bedroom/Grey","").
Ignore column A as its another formula that isnt working as ive removed tabs unneeded. It works when Grey\/Black is in column B, but on the third row down, Grey\/Black appears in column C.

Kim

 

[Fluff]

How about

=IF(AND(COUNTIFS(colour!B2:Z2,"Grey\/Black")>0,COUNTIFS(Room!B2:Z2,"Bedroom")>0),"Bedroom/Grey","")

 

[kradclayton]

Hi!
Thanks that works great!
How would I then extend this formula to look for other IF AND statements? Eg =IF(AND(COUNTIFS(colour!B2:Z2,"Pink")>0,COUNTIFS(Room!B2:Z2,"Bedroom")>0),"Bedroom/Pink","").
Kim

 

[Fluff]

Like

=IF(AND(COUNTIFS(colour!B2:Z2,"Grey\/Black")>0,COUNTIFS(Room!B2:Z2,"Bedroom")>0),"Bedroom/Grey",IF(AND(COUNTIFS(colour!B2:Z2,"Pink")>0,COUNTIFS(Room!B2:Z2,"Bedroom")>0),"Bedroom/Pink",""))