It's because this part

Calculations!$M$2:M$3959={"Ottawa","Toronto"}

will create twice as many TRUE or FALSE
as this

Calculations!$I$2:I$3959="Ontario"

and you cannot use the built in way of SUMPRODUCT with that, try

=SUMPRODUCT(A1:A10,B1:B5)

and it will return a value error

AFAIK you can only use it like


=SUMPRODUCT(--((Calculations!$M$2:M$3959="Ottawa")+(Calculations !$M$2:M$3959="Toronto")0),--(Calculations!$I$2:I$3959="Ontario"))




--
Regards,

Peo Sjoblom








"RagDyeR" wrote in message
...
I'm getting the same #Value! error, and I don't understand it.

I *don't* know why, but this is working, while the other is not!

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

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


"srain001" wrote in message
...
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