| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
