Follow-up to the IF with OR function

K

Katie

Alright, I have another problem. THis is the formula I'm using;

=IF(OR('Location 1'!J2:J2500="Reversed",'Location
1'!F2:F2500="TIL"),"Check",SUM('Location 1'!G:G))

What I want it to do is this; If Anywhere in J2:J2500 it says "Reversed" or
Anywhere in F2:F2500 it says
"TIL" then I want "Check" Displayed.

If not, I want to sum the G column from that same sheet.

This is on another sheet, that's why I have the Sheet name listed.
I tried it with "Til" listed in the F column and it's still giving me the
sum of the G column instead of "Check".

Any ideas would be greatly appreciated.
 
T

tim m

You might just have a bracket in the wrong place. You only want the OR to
affect the 1st part so i might try and stick another ) bracket after TIL and
remove one of the ) from the end.
 
T

Tom Hutchins

Try this:

=IF(OR(ISNUMBER(FIND("Reversed",'Location
1'!J2:J2500)),ISNUMBER(FIND("TIL",'Location
1'!F2:F2500))),"Check",SUM('Location 1'!G:G))

Your formula was testing if all the cells in J2:J2500 contain Reversed, and
if all the cells in H2:H2500 contain TIL. If they don't, that's why it was
always returning the sum of column G (the FALSE condition).

Hope this helps,

Hutch
 
K

Katie

Tom -

This is fabulous- I understand now why it wasn't working- Unfortunately I'm
still receiving an error- any ideas? (i copied and pasted)
 
R

Ragdyer

The formula suggested by Tom is an *array* formula!
--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

If you would prefer a *non-array* formula, you could try this:

=IF(SUMPRODUCT(ISNUMBER(SEARCH("reversed",'Location
1'!J2:J2500))+ISNUMBER(SEARCH("til",'Location
1'!F2:F2500))),"CHECK",SUM('Location 1'!G:G))

If you decide to use the *array* formula, I would suggest, that since the
Find() function is case sensitive, you replace it with the Search() function
which is not case sensitive.
 
Top