LinkBack URL
About LinkBacks
delroba 
Dear all,
i want use the IF Function for a range cells like below table but i think it doesn't work
{=IF(AND(A1:A8>100),"Not Ok","Ok") } -----> Ok
{=IF(COUNTIF(A1:A8,AND(A1:A8>100))>0,"Not Ok","Ok") } -----> Ok
table.JPG
Thank's
Dear all,
i want use the IF Function for a range cells like below table but i think it doesn't work
{=IF(AND(A1:A8>100),"Not Ok","Ok") } -----> Ok
{=IF(COUNTIF(A1:A8,AND(A1:A8>100))>0,"Not Ok","Ok") } -----> Ok
table.JPG
Thank's
You cannot use a range like that in an IF statement. Try something like this instead...
=if(COUNTIF(A1:A8,">100")>0,"Not Ok",""OK")
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
its solved
Thanks a lot
You can if you enter the formula as an array formula**.Originally Posted by FDibbins
=IF(AND(A1:A8>100),"Not Ok","Ok")
Every cell in the range must meet the condition to evaluate as TRUE.
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
But its not work even after press the CTRL,SHIFT,ENTER
{=IF(AND(A1:A8>100),"Not OK","OK")}
the answer is ok either below 100 or not.
why this happened ?
If EVERY cell in the range A1:A8 is greater than 100 then that ARRAY formula should return "Not OK".{=IF(AND(A1:A8>100),"Not OK","OK")}
If ANY cell in the range A1:A8 is less than or equal to 100 then that ARRAY formula should return "OK".
I got it
thanks
Happy to help, thanks for he feedback
oops thanks for the catch, Tony, I meant to include that, but got side-tracked
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks