including two ranges of cells in an IF formaula

[Mark]

I wish to query two ranges of cells. the cells are on two separate worksheets
in the same workbook, but do not know how to specify more than one range in
an IF function. i need the formula to look at both ranges for a particular
value
the ranges are:
'Substance Use'!G9:G71 and 'Mental Health'!G9:G71

please can you advise how to construct the fiormula.
thanks

 

[Guest]

Hi
It depends on what you are wanting. You could use OR or AND functions to do
this but we'd need a bit more info!

Andy.

 

[Bob Phillips]

What do you want to do with the ranges, count a value, compare each , or
what?


--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

 

[Mark]

The cells will contain numbers. I want to identify those cells that contain
"40" and then put a value of 40 in the corresponding cell ie if in 'Substance
Use'!G9 there was a value of 40, i would want to put a value of 40 in
'mysheet'!G9. if there was no value of 40, just add 0. If i was looking at
just one range of figures, the formula would be:
=IF('Substance Use'!G9:G71="40","40","0")

but i also need to look at the other range of cells 'Mental Health'!G9:G71
for the same criteria.

 

[Guest]

Try this:

=IF(OR('Substance Use'!G9:G71="40",'Mental Health'!G9:G71="40"),"40","0")

Andy.

 

[Bob Phillips]

Surely it should be

=IF(OR('Substance Use'!G9="40",'Mental Health'!G9:="40"),"40","0")

in G9 on MySheet and copy down


--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

 

[Mark]

Thank you both. This worked when i removed the speech marks from the numbers
in your formula.
Mark

 

[Guest]

Hey, hold on a minute!!! They were the speech marks from the numbers in YOUR
formula!!

Andy.

 

[Mark]

Hi Andy, my 'formula' was only a guess. i realise now, that you only need
speech marks when specifying text in formaulas.
Mark

 

[Guest]

Only kidding!! ;=)
At least it sorted now, that's all that matters.

Andy.