
Re: Formula help 
Newsgroup: microsoft.public.excel.misc Posted by: RagDyeR 20070519 16:12:57
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 . 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!

Regards,
RD  Please keep all correspondence within the Group, so all may benefit ! 
"Roger Govier" wrote in message news:u9qeEOemHHA.4772@TK2MSFTNGP05.phx.gbl... 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 news:ONkmAtXmHHA.960@TK2MSFTNGP03.phx.gbl... > 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 > news:%238gcmxWmHHA.2452@TK2MSFTNGP04.phx.gbl... >> 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 >> news:Oyz%23kYWmHHA.3760@TK2MSFTNGP05.phx.gbl... >>> 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","Toronto"})*(Calculations!$I$2:I$3935="Ontario")) >>>  >>> >>> Regards, >>> >>> RD >>>  >>> Please keep all correspondence within the Group, so all may benefit >>> ! >>>  >>> >>> >>> "srain001" wrote in message >>> news:75648C1A1CB14E9183851696744F984A@microsoft.com... >>> 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 >>>> news:F4BBD4ACB2144FFBB1FD37C45A2AB537@microsoft.com... >>>> > 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 >>>> > >>>> > >>>> >>>> >>>> >>> >>> >> >> > >
More >>


