If Range =

[AlexJames]

Hi All,

I'm fairly new to this so getting my head around the way excel works has been a steep learning curve, but I'm getting there!

Anyway, my question... what I am trying to do is create something which I thought would be quite simple.

So logically... =IF(R1:R20=1, "Error", "")

Apparently excel doesn't work this way because it doesn't work... what am I doing wrong?

Thanks
Alex

[Sam Capricci]

so you are looking to see if 1 is in that range? try =if(COUNTIF(R1:R20)>0,"Error","")

EDIT, sorry, try it this way... =if(COUNTIF(R1:R20,">"&0)>0,"Error","")

[mangesh.mehendale]

Excel don`t understand the meaning of only Range R1:R20..... You should have to specify like sum(R1:20) or count(R1:R20)....

[AlexJames]

Thanks guys, this works! Much appreciated... slowly getting my head around how this works

I understand how it works in theory, but what I dont get is this little bit in red below... why does it need an &?

=if(COUNTIF(R1:R20,">"&0)>0,"Error","")

[AlexJames]

Why not just

=IF(COUNTIF(R1:R20, ">0","Error","Fleet Code - Enquiry")

[Sam Capricci]

These people explain it better than I can.
it has to do with creating a text string...
http://www.excelforum.com/excel-gene...t-formula.html

[AlexJames]

Fantastic, Ill give it a read!

Really appreciate both of yours help.

[AlexJames]

Ok, along the same line...

IF Range = 1 then Error 1, if Range = 2 then error 2 ... etc...

?

[Debraj Roy]

Hi Alex,

Do you want to check "If nay value is 1 or 2 or..." or you want to check.. "If all values are 1 or 2 or..."

[AlexJames]

Hi Debraj,

Basically what I want is, if the number 10 appears anywhere in column R then cell A1 to say "error". If the number 5 appears, then A1 say "error", if the number 2 appears then say "error"... if nothing appears then ""

Does that make sense?

[Sam Capricci]

The formula I wrote says that if any value greater than 0 appears in that range note as error. Doesn't that satisfy your post #10?

[AlexJames]

Hi Sambo Kid, not quite... only because I realise I need to use multiple error codes for different errors ... anything greater than 0 gives me just 1 error.

So if my range pops up the number 10 ... I know that I have a date mismatch ... the number 5 a name mismatch etc...

[Sam Capricci]

do you still need this to be a range then? Let's say that cell R1 has a 1 but R2 has a 10 and R13 has a 5?
then what would your results be for a range of R1:R20?
maybe it should be individually by cell instead of a range?

[AlexJames]

Humm I see your point... would be 10 times easier to code as well. Ill give it a try and let you know how I get on!

Thank you!

[AlexJames]

How about if I wanted.

If (R1:R20 = anything 'ie text' then "Error", "")

[Sam Capricci]

do you mean is not null? In other words, any cell within the range of R1:R20 has something in it regardless of what it is?

[AlexJames]

Yes thats right

[Sam Capricci]

I worked this one up for your scenario in post # 12...
'=IF(COUNTIF(R1:R20,"="&1)>0,"Error",IF(COUNTIF(R1:R20,"="&5)>0,"name mismatch",IF(COUNTIF(R1:R20,"="&10)>0,"date mismatch","")))
but it assumes you don't have a 1 and a 5 or 10 in that range. Maybe you can use it while I try to write another formula for anything within the range.

[AlexJames]

ok, I managed to get the first line working.. but I need them both to work from the same cell?

=IF(COUNTIF(R:R,"Dealer Mismatch"),"Error", "")
=IF(COUNTIF(R:R,"Date Mismatch"),"Error", "")

[AlexJames]

ooh thank you... Ill check that one out too!

[Sam Capricci]

so for your post # 17, counting anything within the range that is not blank and returning error =IF(COUNTIF(R1:R20,"<>")>0,"error","") should work for you.

[AlexJames]

Does this work for text or just numeric?

[Sam Capricci]

what I gave you in post #21 will work for anything that appears in that range which I wrote based on what i asked you in #16 and your answer in #17.

[AlexJames]

ah gotcha! THank you