Counting values

[Adam1 Chicago]

If I have a column of numbers ranging from 0 to 100, what function can I use
to say in a single cell: "if any value in the column is greater than 6,
return "WARNING", otherwise return nul". Thank you

 

[Max]

Try: =IF(COUNTIF(A1:A100,">6"),"Warning","")

 

[Sandy Mann]

Try: =IF(COUNTIF(A1:A100,">6"),"Warning","")

That may very well be what the OP wanted ie give a warning if there are more
than 6 different values. I read it differently in that I thought the OP
wanted a warning when the count of the numbers of any one value was greater
than 6 ie say 7 number 10's.

=IF(MAX(COUNTIF(A1:A1000,ROW(INDIRECT("1:100"))))>5,"Warning","")

which is an array formula - entered with Control + Shift + Enter

Or I thought it was just possible then he may have meant any value was
higher than 6 in which case it would be:

=IF(MAX(A1:A1000)>6,"Warning","")

Between the three surely we have the answer somewhere <g>

Regards

Sandy

 

[Adam1 Chicago]

thank you, that was very helpful.

do you know if there is a way i can replce the "6" below with a cell
reference?

for example: =if(countif(a1:a100,">X64"),"Warning","")

thanks

 

[Adam1 Chicago]

Yes, you got it. Thank you both

That may very well be what the OP wanted ie give a warning if there are more
than 6 different values. I read it differently in that I thought the OP
wanted a warning when the count of the numbers of any one value was greater
than 6 ie say 7 number 10's.

=IF(MAX(COUNTIF(A1:A1000,ROW(INDIRECT("1:100"))))>5,"Warning","")

which is an array formula - entered with Control + Shift + Enter

Or I thought it was just possible then he may have meant any value was
higher than 6 in which case it would be:

=IF(MAX(A1:A1000)>6,"Warning","")

Between the three surely we have the answer somewhere <g>

Regards

Sandy

 

[Jay]

do you know if there is a way i can replce the "6" below with a cell

reference?

for example: =if(countif(a1:a100,">X64"),"Warning","")

How about this:
=if(MAX(a1:a100)>X64,"Warning","")

 

[Max]

do you know if there is a way i can replace

the "6" below with a cell reference?
=if(countif(a1:a100,">X64"),"Warning","")

To point to cell references, use in this way:
=IF(COUNTIF(A1:A100,">"&X64),"Warning","")
where X64 contains the number

 

[Max]

Between the three surely

we have the answer somewhere <g>

Nothing could have survived the fusillade <g>

Your 1st interp on the OP and the suggestion
was a good one, IMHO
(It wouldn't have crossed my mind ..)

 

[Pat Z.]

Can I add a question to this post?
How would you count if you are looking at a range of numbers, i.e. >=8000
and <=9999?

 

[JE McGimpsey]

one way:

=IF(SUMPRODUCT(--(A1:A100>=8000),--(A1:A100<=9999))>0,"Warning","")


For an explanation of the "--" see

http://www.mcgimpsey.com/excel/doubleneg.html