if statement using multiple cells..

Assuming your cells are A1:D1

=IF(COUNTIF(A1:D1,2)=4,1,LOOKUP(COUNTIF(A1:D1,1),{0,1,2,3,4},{"",5,4,3,2}))

Thanks for you quick responce, it's not bringing back my required values can you explain to me what the formula is doing?? I can try and amend it then, sorry if I sound dim!!

I think I may not have helped myself earlier - this is what im after...

if all 4 individual cells contain the number 2 then "1"
if all 4 individual cells contain the number 1 then "2"
if any one of the cells contain 0 then "3"
if 2 of the cells contain 0 "4"
if only 1 cell contains the number 1 or 2 then "5"

OK, so your new conditions aren't mutually exclusive, I presume the order in which they're written determines the order of precedence, e.g. what result do you expect if 2 cells contain a zero and 1 contains a 1? I assume 4

What result do you expect if one cell is blank and the other 3 contain 1s?

Your expectation on the "4" result is correct.

There would never be a blank cell, however if three cells contain "1" or "2" and the other contains 0 then I would expect "5" to be the result...

Daddylonglegs I've done it!!!!

Thanks for starting me off on the right foot - much appreciated!

Not sure if this is the most succint way to right what i need but it works so I'll take it.

=if(COUNTIF(G40:J40,1)=4,2,IF(COUNTIF(G40:J40,2)=4,1,IF(COUNTIF(G40:J40,0)=4,5,IF(COUNTIF(G40:J40,0)=3,5,IF(COUNTIF(G40:J40,0)=2,4,IF(COUNTIF(G40:J40,0)=1,3,IF(COUNTIF(G40:J40,0)=0,2)))))))

Thanks again