asked on

Excel: Check multiple ranges for values within.

In Excel 2007 what formula would I put in cell A2 in order to achieve the following...

If any of the cells in the following ranges: A2:V6, B20, Z26, AA46:AC54 contain "hello" (lower case only) then show the following message "True", otherwise "False".

Example: =if(CountIf(A2:V6, B20, Z26, AA46:AC54, "hello")>0,"True","False") does not work.

Is my syntax wrong or will Countif not allow me to check multiple ranges?
I have tried to use Countif, but I can't get the syntax to work with multiple ranges.
The shortest and most efficient formula wins! :)

ASKER CERTIFIED SOLUTION

[ fanpages ]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

[ fanpages ]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

barry houdini

Looks good to me, fp!

regards, barry

[ fanpages ]

:) Thanks barry!

ouestque

ASKER

Thanks so much!! Brilliant! I'm trying to accept your answers on my iPhone 4s using Safari, but the comments box is covering the grade. Because I can't set your grade it won't let me submit. I want to give y'all an A++!!! Should we let the administrators know? Meanwhile I'll try to accept your answers when I get on my laptop at home.

ouestque

ASKER

Thanks so much!!! Sorry about the delay.

[ fanpages ]

I had the misfortune to own an iPhone 3GS a few years ago... No need to apologise! :)