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

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

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

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

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

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

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

But Peo, that's not telling me why the asterisk form *does* work!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" wrote in message
...
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

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

Hi Rick and Peo

Firstly apologies to the OP, I should have changed the double unaries to
asterisk, not just copied his formula and placed the array around the
two cities.

Peo, the arrays are of the same size, although one of them could have
two outcomes True or False.
These are mutually exclusive however for each cell within the range.

When you use the double unary minus, however, each True or False is
converted instantly into a 1 or 0, so you do end up with a problem.

When you use the asterisk, the coercion to 1's and 0's doesn't take
place until both sets of criteria have been evaluated
Consider the following small example
=SUMPRODUCT(($C$2:C$4={"Ottawa","Toronto"})*($D$2: $D$4="Ontario"))
With C2=Ottawa, C3 =blank and C4 =Toronto
With D2=Ontario, D3 =blank and D4 =Toronto

The result from the first test is
True, False; False, False; False, True
and from the second it is
True; False; True
Note the semicolons as compared with the commas.

Now, when these two arrays are multiplied together with the asterisk,,
only then do we get
1, 0; 0, 0; 0, 1
which sums to 2

With the double unary, the first set of True/False's are changed to
{1, 0; 0, 0; 0, 1}
whilst the second term is still --{True;False;True}
so for the next part of the evaluation, Sumproduct does object because
there are twice as many values in the first term as in the second.

So, I believe it is all due to the order of coercion.

--
Regards

Roger Govier


"Ragdyer" wrote in message
...
But Peo, that's not telling me why the asterisk form *does* work!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" wrote in message
...
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

That sounds perfectly logical to me Roger.
Thanks for the explanation.

What I usually do in these situations is select a single argument in the
formula bar, and then evaluate it with <F9.
But since I used the OP's original range, and got the "Formula Too Long"
error message, I immediately truncated the formula range in order to get a
reading.

I just cut it too small.

Looking at it this morning, before reading your post, it was there, staring
me in the face, BUT ... I just didn't see it.

I just not as wide awake as you are!<bg

--

Regards,

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


"Roger Govier" wrote in message
...
Hi Rick and Peo

Firstly apologies to the OP, I should have changed the double unaries to
asterisk, not just copied his formula and placed the array around the
two cities.

Peo, the arrays are of the same size, although one of them could have
two outcomes True or False.
These are mutually exclusive however for each cell within the range.

When you use the double unary minus, however, each True or False is
converted instantly into a 1 or 0, so you do end up with a problem.

When you use the asterisk, the coercion to 1's and 0's doesn't take
place until both sets of criteria have been evaluated
Consider the following small example
=SUMPRODUCT(($C$2:C$4={"Ottawa","Toronto"})*($D$2: $D$4="Ontario"))
With C2=Ottawa, C3 =blank and C4 =Toronto
With D2=Ontario, D3 =blank and D4 =Toronto

The result from the first test is
True, False; False, False; False, True
and from the second it is
True; False; True
Note the semicolons as compared with the commas.

Now, when these two arrays are multiplied together with the asterisk,,
only then do we get
1, 0; 0, 0; 0, 1
which sums to 2

With the double unary, the first set of True/False's are changed to
{1, 0; 0, 0; 0, 1}
whilst the second term is still --{True;False;True}
so for the next part of the evaluation, Sumproduct does object because
there are twice as many values in the first term as in the second.

So, I believe it is all due to the order of coercion.

--
Regards

Roger Govier


"Ragdyer" wrote in message
...
But Peo, that's not telling me why the asterisk form *does* work!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" wrote in message
...
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

BTW ... Just *another* chit in the
"Use Asterisk" column for SumProduct!<vbg
--

Regards,

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

"RagDyeR" wrote in message
...
That sounds perfectly logical to me Roger.
Thanks for the explanation.

What I usually do in these situations is select a single argument in the
formula bar, and then evaluate it with <F9.
But since I used the OP's original range, and got the "Formula Too Long"
error message, I immediately truncated the formula range in order to get a
reading.

I just cut it too small.

Looking at it this morning, before reading your post, it was there, staring
me in the face, BUT ... I just didn't see it.

I just not as wide awake as you are!<bg

--

Regards,

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


"Roger Govier" wrote in message
...
Hi Rick and Peo

Firstly apologies to the OP, I should have changed the double unaries to
asterisk, not just copied his formula and placed the array around the
two cities.

Peo, the arrays are of the same size, although one of them could have
two outcomes True or False.
These are mutually exclusive however for each cell within the range.

When you use the double unary minus, however, each True or False is
converted instantly into a 1 or 0, so you do end up with a problem.

When you use the asterisk, the coercion to 1's and 0's doesn't take
place until both sets of criteria have been evaluated
Consider the following small example
=SUMPRODUCT(($C$2:C$4={"Ottawa","Toronto"})*($D$2: $D$4="Ontario"))
With C2=Ottawa, C3 =blank and C4 =Toronto
With D2=Ontario, D3 =blank and D4 =Toronto

The result from the first test is
True, False; False, False; False, True
and from the second it is
True; False; True
Note the semicolons as compared with the commas.

Now, when these two arrays are multiplied together with the asterisk,,
only then do we get
1, 0; 0, 0; 0, 1
which sums to 2

With the double unary, the first set of True/False's are changed to
{1, 0; 0, 0; 0, 1}
whilst the second term is still --{True;False;True}
so for the next part of the evaluation, Sumproduct does object because
there are twice as many values in the first term as in the second.

So, I believe it is all due to the order of coercion.

--
Regards

Roger Govier


"Ragdyer" wrote in message
...
But Peo, that's not telling me why the asterisk form *does* work!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" wrote in message
...
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

Hi Rick

What version of XL are you using?
The Evaluate Formula tool came in with XL2002 - ToolsFormula
AuditingEvaluate Formula

I have this dragged as a separate icon to my toolbar in 2002 and 2003
(it is there by default in 2007)
Again, cut the range to manageable proportions and step through the
Evaluation - it usually helps to see where the problems are arising.

BTW, I agree with the extra tick!!<bg

--
Regards

Roger Govier


"RagDyeR" wrote in message
...
That sounds perfectly logical to me Roger.
Thanks for the explanation.

What I usually do in these situations is select a single argument in
the
formula bar, and then evaluate it with <F9.
But since I used the OP's original range, and got the "Formula Too
Long"
error message, I immediately truncated the formula range in order to
get a
reading.

I just cut it too small.

Looking at it this morning, before reading your post, it was there,
staring
me in the face, BUT ... I just didn't see it.

I just not as wide awake as you are!<bg

--

Regards,

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


"Roger Govier" wrote in message
...
Hi Rick and Peo

Firstly apologies to the OP, I should have changed the double unaries
to
asterisk, not just copied his formula and placed the array around the
two cities.

Peo, the arrays are of the same size, although one of them could have
two outcomes True or False.
These are mutually exclusive however for each cell within the range.

When you use the double unary minus, however, each True or False is
converted instantly into a 1 or 0, so you do end up with a problem.

When you use the asterisk, the coercion to 1's and 0's doesn't take
place until both sets of criteria have been evaluated
Consider the following small example
=SUMPRODUCT(($C$2:C$4={"Ottawa","Toronto"})*($D$2: $D$4="Ontario"))
With C2=Ottawa, C3 =blank and C4 =Toronto
With D2=Ontario, D3 =blank and D4 =Toronto

The result from the first test is
True, False; False, False; False, True
and from the second it is
True; False; True
Note the semicolons as compared with the commas.

Now, when these two arrays are multiplied together with the asterisk,,
only then do we get
1, 0; 0, 0; 0, 1
which sums to 2

With the double unary, the first set of True/False's are changed to
{1, 0; 0, 0; 0, 1}
whilst the second term is still --{True;False;True}
so for the next part of the evaluation, Sumproduct does object because
there are twice as many values in the first term as in the second.

So, I believe it is all due to the order of coercion.

--
Regards

Roger Govier


"Ragdyer" wrote in message
...
But Peo, that's not telling me why the asterisk form *does* work!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" wrote in message
...
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

That was done on an XL97 machine.
The <F9 on selected portions of a formula works pretty well, although you
do have to pay attention to exactly what you're doing (and notice the
punctuation).<g

--
Regards,

RD

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

"Roger Govier" wrote in message
...
Hi Rick

What version of XL are you using?
The Evaluate Formula tool came in with XL2002 - ToolsFormula
AuditingEvaluate Formula

I have this dragged as a separate icon to my toolbar in 2002 and 2003
(it is there by default in 2007)
Again, cut the range to manageable proportions and step through the
Evaluation - it usually helps to see where the problems are arising.

BTW, I agree with the extra tick!!<bg

--
Regards

Roger Govier


"RagDyeR" wrote in message
...
That sounds perfectly logical to me Roger.
Thanks for the explanation.

What I usually do in these situations is select a single argument in
the
formula bar, and then evaluate it with <F9.
But since I used the OP's original range, and got the "Formula Too
Long"
error message, I immediately truncated the formula range in order to
get a
reading.

I just cut it too small.

Looking at it this morning, before reading your post, it was there,
staring
me in the face, BUT ... I just didn't see it.

I just not as wide awake as you are!<bg

--

Regards,

RD

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

--------------------------------------------------------------------------
---------------------


"Roger Govier" wrote in message
...
Hi Rick and Peo

Firstly apologies to the OP, I should have changed the double unaries
to
asterisk, not just copied his formula and placed the array around the
two cities.

Peo, the arrays are of the same size, although one of them could have
two outcomes True or False.
These are mutually exclusive however for each cell within the range.

When you use the double unary minus, however, each True or False is
converted instantly into a 1 or 0, so you do end up with a problem.

When you use the asterisk, the coercion to 1's and 0's doesn't take
place until both sets of criteria have been evaluated
Consider the following small example
=SUMPRODUCT(($C$2:C$4={"Ottawa","Toronto"})*($D$2: $D$4="Ontario"))
With C2=Ottawa, C3 =blank and C4 =Toronto
With D2=Ontario, D3 =blank and D4 =Toronto

The result from the first test is
True, False; False, False; False, True
and from the second it is
True; False; True
Note the semicolons as compared with the commas.

Now, when these two arrays are multiplied together with the asterisk,,
only then do we get
1, 0; 0, 0; 0, 1
which sums to 2

With the double unary, the first set of True/False's are changed to
{1, 0; 0, 0; 0, 1}
whilst the second term is still --{True;False;True}
so for the next part of the evaluation, Sumproduct does object because
there are twice as many values in the first term as in the second.

So, I believe it is all due to the order of coercion.

--
Regards

Roger Govier


"Ragdyer" wrote in message
...
But Peo, that's not telling me why the asterisk form *does* work!
--
Regards,

RD


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

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