Counting values
 

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

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Adam1 Chicago wrote in message
...
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" wrote in message
...
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

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"Max" wrote in message
...
Try: =IF(COUNTIF(A1:A100,"6"),"Warning","")

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Adam1 Chicago wrote in message
...
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

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


"Max" wrote:

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Adam1 Chicago wrote in message
...
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

Yes, you got it. Thank you both

"Sandy Mann" wrote:

"Max" wrote in message
...
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

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"Max" wrote in message
...
Try: =IF(COUNTIF(A1:A100,"6"),"Warning","")

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Adam1 Chicago wrote in message
...
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

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","")

do you know if there is a way i can replace=20
Adam1 Chicago wrote:
the "6" below with a cell reference?
=3Dif(countif(a1:a100,"X64"),"Warning","")

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

--
Rgds
Max
xl 97
---
GMT+8, 1=B0 22' N 103=B0 45' E
xdemechanik <atyahoo<dotcom
----

Between the three surely=20
we have the answer somewhere <g

Nothing could have survived the fusillade <g

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

--
Rgds
Max
xl 97
---
GMT+8, 1=B0 22' N 103=B0 45' E
xdemechanik <atyahoo<dotcom
----

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?


"Max" wrote:

do you know if there is a way i can replace
Adam1 Chicago wrote:
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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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


In article ,
Pat Z. wrote:

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?