Quite right Dave, totally untrustworthy<vbg

see response to Peo / RagDyer

--
Regards

Roger Govier


"Dave Peterson" wrote in message
...
Ignore my suggestion.

I shouldn't have trusted Roger's response <vvbg.


Dave Peterson wrote:

Do you have any errors in M2:M3935 or I2:I3935?

Did you change the formula? You may want to post what you used--or
try Roger's
suggestion once more.

srain001 wrote:

Hi,

Unfortunately, this isn't working. I get a result of #VALUE in the
cell.
I'm referencing the right columns and the spelling is correct, so
I'm
baffled...

Any other ideas?

Thanx
S.

"Roger Govier" wrote:

Hi

Create an array of Ottawa and Toronto, which is saying either
Ottawa OR
Toronto.

=SUMPRODUCT(--(Calculations!$M$2:M$3935={"Ottawa","Toronto"}),
--(Calculations!$I$2:I$3935="Ontario"))

--
Regards

Roger Govier


"srain001" wrote in message
...
Hello,

I am looking for a way to capture info from one column while
referring
it to
another. For example, the info I'm analyzing pertains to
cities and
the
provinces in which they're located. I'd like to find a way in
which I
can
capture instances in which multiple cells in Column A refer to
one
cell in
Column B. (i.e. Ottawa and Toronto are both located in
Ontario) I've
tried
a SUMPRODUCT formula, but it won't let me put multiple
instances in
the same
calculation. Is there something else I can try?

This is what I'm currently trying, but unable to make work:
=SUMPRODUCT(--(Calculations!$M$2:M$3935="Ottawa","Toronto"),--(Calculations!$I$2:I$3935="Ontario"))

When I try it with simply "Ottawa" it works, but when I try to
add
another
city, an error appears.

This is an example of the data I have:

Column A
Hafford
Ottawa
Toronto
Montreal

Column B

Saskatchewan
Ontario
Quebec


Thanx!
Srain






--

Dave Peterson

--

Dave Peterson