Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Matches, then Sumproduct of Values
Am trying to come up with a way to match names in Column A of Sheet2 with
names in Column E of Sheet1. If there is a match there, then match names in Column C of Sheet2 with names in Column C of Sheet3, and sum the values, in Column V of Sheet1, that match this name! Damn, this is tough! Im working with this: =SUMPRODUCT(--(Sheet2!$A$2:$A$678=Sheet1!$E$2:$E$678),SUMPRODUCT (Sheet1!$C$2:$C$678=C64),Sheet1!$V$2:$V$678) I keep getting a #VALUE! result. Sumproduct can be used up to 30 times in a single function, right. What am I doing wrong? Thanks, Ryan--- -- RyGuy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Matches, then Sumproduct of Values
Maybe this:
=SUMPRODUCT(--(Sheet2!$A$2:$A$678=Sheet1!$E$2:$E$678),--(Sheet1!$C$2:$C$678=C64),Sheet1!$V$2:$V$678) -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... Am trying to come up with a way to match names in Column A of Sheet2 with names in Column E of Sheet1. If there is a match there, then match names in Column C of Sheet2 with names in Column C of Sheet3, and sum the values, in Column V of Sheet1, that match this name! Damn, this is tough! I'm working with this: =SUMPRODUCT(--(Sheet2!$A$2:$A$678=Sheet1!$E$2:$E$678),SUMPRODUCT (Sheet1!$C$2:$C$678=C64),Sheet1!$V$2:$V$678) I keep getting a #VALUE! result. Sumproduct can be used up to 30 times in a single function, right. What am I doing wrong? Thanks, Ryan--- -- RyGuy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Matches, then Sumproduct of Values
Thanks Biff! That's pretty close, but it's not doing what I really want it
to do. If I have $1,200 in Cell V2 and $1,800 in Cell V3, and a name, Dave in Cell C3, I see $1,800. If I move Dave to Cell C2, I see $1,200. I wanted Dave in any Cell in Column C, (down to row 678) and I would see $3,000. I committed with CSE; still didn't give me the results I was seeking. Any thoughts? Thanks, Ryan--- -- RyGuy "T. Valko" wrote: Maybe this: =SUMPRODUCT(--(Sheet2!$A$2:$A$678=Sheet1!$E$2:$E$678),--(Sheet1!$C$2:$C$678=C64),Sheet1!$V$2:$V$678) -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... Am trying to come up with a way to match names in Column A of Sheet2 with names in Column E of Sheet1. If there is a match there, then match names in Column C of Sheet2 with names in Column C of Sheet3, and sum the values, in Column V of Sheet1, that match this name! Damn, this is tough! I'm working with this: =SUMPRODUCT(--(Sheet2!$A$2:$A$678=Sheet1!$E$2:$E$678),SUMPRODUCT (Sheet1!$C$2:$C$678=C64),Sheet1!$V$2:$V$678) I keep getting a #VALUE! result. Sumproduct can be used up to 30 times in a single function, right. What am I doing wrong? Thanks, Ryan--- -- RyGuy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Matches, then Sumproduct of Values
Can you post a *small* data sample that shows us what you want?
-- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... Thanks Biff! That's pretty close, but it's not doing what I really want it to do. If I have $1,200 in Cell V2 and $1,800 in Cell V3, and a name, Dave in Cell C3, I see $1,800. If I move Dave to Cell C2, I see $1,200. I wanted Dave in any Cell in Column C, (down to row 678) and I would see $3,000. I committed with CSE; still didn't give me the results I was seeking. Any thoughts? Thanks, Ryan--- -- RyGuy "T. Valko" wrote: Maybe this: =SUMPRODUCT(--(Sheet2!$A$2:$A$678=Sheet1!$E$2:$E$678),--(Sheet1!$C$2:$C$678=C64),Sheet1!$V$2:$V$678) -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... Am trying to come up with a way to match names in Column A of Sheet2 with names in Column E of Sheet1. If there is a match there, then match names in Column C of Sheet2 with names in Column C of Sheet3, and sum the values, in Column V of Sheet1, that match this name! Damn, this is tough! I'm working with this: =SUMPRODUCT(--(Sheet2!$A$2:$A$678=Sheet1!$E$2:$E$678),SUMPRODUCT (Sheet1!$C$2:$C$678=C64),Sheet1!$V$2:$V$678) I keep getting a #VALUE! result. Sumproduct can be used up to 30 times in a single function, right. What am I doing wrong? Thanks, Ryan--- -- RyGuy |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Matches, then Sumproduct of Values
Here is a sample:
http://rs173.rapidshare.com/files/196921830/sample.xls I was trying to get Cell M2 to be 4500. Thanks, Ryan-- -- RyGuy "T. Valko" wrote: Can you post a *small* data sample that shows us what you want? -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... Thanks Biff! That's pretty close, but it's not doing what I really want it to do. If I have $1,200 in Cell V2 and $1,800 in Cell V3, and a name, Dave in Cell C3, I see $1,800. If I move Dave to Cell C2, I see $1,200. I wanted Dave in any Cell in Column C, (down to row 678) and I would see $3,000. I committed with CSE; still didn't give me the results I was seeking. Any thoughts? Thanks, Ryan--- -- RyGuy "T. Valko" wrote: Maybe this: =SUMPRODUCT(--(Sheet2!$A$2:$A$678=Sheet1!$E$2:$E$678),--(Sheet1!$C$2:$C$678=C64),Sheet1!$V$2:$V$678) -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... Am trying to come up with a way to match names in Column A of Sheet2 with names in Column E of Sheet1. If there is a match there, then match names in Column C of Sheet2 with names in Column C of Sheet3, and sum the values, in Column V of Sheet1, that match this name! Damn, this is tough! I'm working with this: =SUMPRODUCT(--(Sheet2!$A$2:$A$678=Sheet1!$E$2:$E$678),SUMPRODUCT (Sheet1!$C$2:$C$678=C64),Sheet1!$V$2:$V$678) I keep getting a #VALUE! result. Sumproduct can be used up to 30 times in a single function, right. What am I doing wrong? Thanks, Ryan--- -- RyGuy |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Matches, then Sumproduct of Values
Ok, on Sheet1 column C, you'd need to have the rep name in each cell that
applies. As is, there's no way to associate the 1200 & 1800 with Andy. -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... Here is a sample: http://rs173.rapidshare.com/files/196921830/sample.xls I was trying to get Cell M2 to be 4500. Thanks, Ryan-- -- RyGuy "T. Valko" wrote: Can you post a *small* data sample that shows us what you want? -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... Thanks Biff! That's pretty close, but it's not doing what I really want it to do. If I have $1,200 in Cell V2 and $1,800 in Cell V3, and a name, Dave in Cell C3, I see $1,800. If I move Dave to Cell C2, I see $1,200. I wanted Dave in any Cell in Column C, (down to row 678) and I would see $3,000. I committed with CSE; still didn't give me the results I was seeking. Any thoughts? Thanks, Ryan--- -- RyGuy "T. Valko" wrote: Maybe this: =SUMPRODUCT(--(Sheet2!$A$2:$A$678=Sheet1!$E$2:$E$678),--(Sheet1!$C$2:$C$678=C64),Sheet1!$V$2:$V$678) -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... Am trying to come up with a way to match names in Column A of Sheet2 with names in Column E of Sheet1. If there is a match there, then match names in Column C of Sheet2 with names in Column C of Sheet3, and sum the values, in Column V of Sheet1, that match this name! Damn, this is tough! I'm working with this: =SUMPRODUCT(--(Sheet2!$A$2:$A$678=Sheet1!$E$2:$E$678),SUMPRODUCT (Sheet1!$C$2:$C$678=C64),Sheet1!$V$2:$V$678) I keep getting a #VALUE! result. Sumproduct can be used up to 30 times in a single function, right. What am I doing wrong? Thanks, Ryan--- -- RyGuy |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Matches, then Sumproduct of Values
ryguy7272 wrote:
Here is a sample: http://rs173.rapidshare.com/files/196921830/sample.xls I was trying to get Cell M2 to be 4500. Thanks, Ryan-- You would need "Andy D Fox" in C2 and C3 to get that answer. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Matches, then Sumproduct of Values
Yes, adding those names (Andy, Dave, etc.) in there was the only way I could
get the values I was looking for. However, those names will only apprear once. I thought the Sumproduct, inside the array, picked up all incidences of data (names, numbers, etc.) Is there a workaround? Is there another way to do this? I'm sure there is a VBA solution (Union, probably), but the guy that I am doing this for is definitely VBA savvy. I wanted to give him a function that he will be able to maintain. Any other thoughs or is this a dead end? Thanks, Ryan-- -- RyGuy "Glenn" wrote: ryguy7272 wrote: Here is a sample: http://rs173.rapidshare.com/files/196921830/sample.xls I was trying to get Cell M2 to be 4500. Thanks, Ryan-- You would need "Andy D Fox" in C2 and C3 to get that answer. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Matches, then Sumproduct of Values
Wait, I think I got it! This seems to work:
=SUMPRODUCT(--(Sheet2!$A$2:$A$678=Sheet1!$E$2:$E$678),--(Sheet1!$C$2:$C$678=C2),Sheet1!$V$2:$V$678) It's net even an array... -- RyGuy "ryguy7272" wrote: Yes, adding those names (Andy, Dave, etc.) in there was the only way I could get the values I was looking for. However, those names will only apprear once. I thought the Sumproduct, inside the array, picked up all incidences of data (names, numbers, etc.) Is there a workaround? Is there another way to do this? I'm sure there is a VBA solution (Union, probably), but the guy that I am doing this for is definitely VBA savvy. I wanted to give him a function that he will be able to maintain. Any other thoughs or is this a dead end? Thanks, Ryan-- -- RyGuy "Glenn" wrote: ryguy7272 wrote: Here is a sample: http://rs173.rapidshare.com/files/196921830/sample.xls I was trying to get Cell M2 to be 4500. Thanks, Ryan-- You would need "Andy D Fox" in C2 and C3 to get that answer. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Matches, then Sumproduct of Values
That's the same syntax I suggested in my first reply but you said it didn't
work. Based on the setup and data of your sample that won't work. ??? -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... Wait, I think I got it! This seems to work: =SUMPRODUCT(--(Sheet2!$A$2:$A$678=Sheet1!$E$2:$E$678),--(Sheet1!$C$2:$C$678=C2),Sheet1!$V$2:$V$678) It's net even an array... -- RyGuy "ryguy7272" wrote: Yes, adding those names (Andy, Dave, etc.) in there was the only way I could get the values I was looking for. However, those names will only apprear once. I thought the Sumproduct, inside the array, picked up all incidences of data (names, numbers, etc.) Is there a workaround? Is there another way to do this? I'm sure there is a VBA solution (Union, probably), but the guy that I am doing this for is definitely VBA savvy. I wanted to give him a function that he will be able to maintain. Any other thoughs or is this a dead end? Thanks, Ryan-- -- RyGuy "Glenn" wrote: ryguy7272 wrote: Here is a sample: http://rs173.rapidshare.com/files/196921830/sample.xls I was trying to get Cell M2 to be 4500. Thanks, Ryan-- You would need "Andy D Fox" in C2 and C3 to get that answer. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Matches, then Sumproduct of Values
I have to recant my last statement; it is NOT working. Dang!! I could swear
it was working a couple hours ago. Is there any way to do what I am trying to do? If I add in a few extra names (Andy, Dave, etc.) I can get the desired results... The thing is, I will only have the name one time in that Column C. Appreciate any help with this. Thanks so much, Ryan--- -- RyGuy "ryguy7272" wrote: Wait, I think I got it! This seems to work: =SUMPRODUCT(--(Sheet2!$A$2:$A$678=Sheet1!$E$2:$E$678),--(Sheet1!$C$2:$C$678=C2),Sheet1!$V$2:$V$678) It's net even an array... -- RyGuy "ryguy7272" wrote: Yes, adding those names (Andy, Dave, etc.) in there was the only way I could get the values I was looking for. However, those names will only apprear once. I thought the Sumproduct, inside the array, picked up all incidences of data (names, numbers, etc.) Is there a workaround? Is there another way to do this? I'm sure there is a VBA solution (Union, probably), but the guy that I am doing this for is definitely VBA savvy. I wanted to give him a function that he will be able to maintain. Any other thoughs or is this a dead end? Thanks, Ryan-- -- RyGuy "Glenn" wrote: ryguy7272 wrote: Here is a sample: http://rs173.rapidshare.com/files/196921830/sample.xls I was trying to get Cell M2 to be 4500. Thanks, Ryan-- You would need "Andy D Fox" in C2 and C3 to get that answer. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Matches, then Sumproduct of Values
Maybe I'll be able to figure something out if I can see the actual file.
Want me to take a look? -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I have to recant my last statement; it is NOT working. Dang!! I could swear it was working a couple hours ago. Is there any way to do what I am trying to do? If I add in a few extra names (Andy, Dave, etc.) I can get the desired results... The thing is, I will only have the name one time in that Column C. Appreciate any help with this. Thanks so much, Ryan--- -- RyGuy "ryguy7272" wrote: Wait, I think I got it! This seems to work: =SUMPRODUCT(--(Sheet2!$A$2:$A$678=Sheet1!$E$2:$E$678),--(Sheet1!$C$2:$C$678=C2),Sheet1!$V$2:$V$678) It's net even an array... -- RyGuy "ryguy7272" wrote: Yes, adding those names (Andy, Dave, etc.) in there was the only way I could get the values I was looking for. However, those names will only apprear once. I thought the Sumproduct, inside the array, picked up all incidences of data (names, numbers, etc.) Is there a workaround? Is there another way to do this? I'm sure there is a VBA solution (Union, probably), but the guy that I am doing this for is definitely VBA savvy. I wanted to give him a function that he will be able to maintain. Any other thoughs or is this a dead end? Thanks, Ryan-- -- RyGuy "Glenn" wrote: ryguy7272 wrote: Here is a sample: http://rs173.rapidshare.com/files/196921830/sample.xls I was trying to get Cell M2 to be 4500. Thanks, Ryan-- You would need "Andy D Fox" in C2 and C3 to get that answer. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Matches, then Sumproduct of Values
You've helped me many , many, many times before, Biff. I appreciate the help
thins time; I think this one may be outside the capabilities of Excel's predefined functions. I'm going to the Programming area and look for a UDF, or if I can't find anything, ask for help from the experts there. I'm not very good with developing UDFs. If you can think of something, please post back. Please don't spend a lot of time on it though!! Thanks for everything!! Ryan-- -- RyGuy "T. Valko" wrote: Maybe I'll be able to figure something out if I can see the actual file. Want me to take a look? -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I have to recant my last statement; it is NOT working. Dang!! I could swear it was working a couple hours ago. Is there any way to do what I am trying to do? If I add in a few extra names (Andy, Dave, etc.) I can get the desired results... The thing is, I will only have the name one time in that Column C. Appreciate any help with this. Thanks so much, Ryan--- -- RyGuy "ryguy7272" wrote: Wait, I think I got it! This seems to work: =SUMPRODUCT(--(Sheet2!$A$2:$A$678=Sheet1!$E$2:$E$678),--(Sheet1!$C$2:$C$678=C2),Sheet1!$V$2:$V$678) It's net even an array... -- RyGuy "ryguy7272" wrote: Yes, adding those names (Andy, Dave, etc.) in there was the only way I could get the values I was looking for. However, those names will only apprear once. I thought the Sumproduct, inside the array, picked up all incidences of data (names, numbers, etc.) Is there a workaround? Is there another way to do this? I'm sure there is a VBA solution (Union, probably), but the guy that I am doing this for is definitely VBA savvy. I wanted to give him a function that he will be able to maintain. Any other thoughs or is this a dead end? Thanks, Ryan-- -- RyGuy "Glenn" wrote: ryguy7272 wrote: Here is a sample: http://rs173.rapidshare.com/files/196921830/sample.xls I was trying to get Cell M2 to be 4500. Thanks, Ryan-- You would need "Andy D Fox" in C2 and C3 to get that answer. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Matches, then Sumproduct of Values
Good luck!
-- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... You've helped me many , many, many times before, Biff. I appreciate the help thins time; I think this one may be outside the capabilities of Excel's predefined functions. I'm going to the Programming area and look for a UDF, or if I can't find anything, ask for help from the experts there. I'm not very good with developing UDFs. If you can think of something, please post back. Please don't spend a lot of time on it though!! Thanks for everything!! Ryan-- -- RyGuy "T. Valko" wrote: Maybe I'll be able to figure something out if I can see the actual file. Want me to take a look? -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I have to recant my last statement; it is NOT working. Dang!! I could swear it was working a couple hours ago. Is there any way to do what I am trying to do? If I add in a few extra names (Andy, Dave, etc.) I can get the desired results... The thing is, I will only have the name one time in that Column C. Appreciate any help with this. Thanks so much, Ryan--- -- RyGuy "ryguy7272" wrote: Wait, I think I got it! This seems to work: =SUMPRODUCT(--(Sheet2!$A$2:$A$678=Sheet1!$E$2:$E$678),--(Sheet1!$C$2:$C$678=C2),Sheet1!$V$2:$V$678) It's net even an array... -- RyGuy "ryguy7272" wrote: Yes, adding those names (Andy, Dave, etc.) in there was the only way I could get the values I was looking for. However, those names will only apprear once. I thought the Sumproduct, inside the array, picked up all incidences of data (names, numbers, etc.) Is there a workaround? Is there another way to do this? I'm sure there is a VBA solution (Union, probably), but the guy that I am doing this for is definitely VBA savvy. I wanted to give him a function that he will be able to maintain. Any other thoughs or is this a dead end? Thanks, Ryan-- -- RyGuy "Glenn" wrote: ryguy7272 wrote: Here is a sample: http://rs173.rapidshare.com/files/196921830/sample.xls I was trying to get Cell M2 to be 4500. Thanks, Ryan-- You would need "Andy D Fox" in C2 and C3 to get that answer. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Matches, then Sumproduct of Values
P.S.
Here's what I'm thinking... If Sheet1 was something like this: .....C.........E......V Andy...Carat...10 ............Carat...20 ............Carat...15 Bill......X.........10 ............X.........10 Lisa....Y.........22 ...........Y.........17 Then we should be able to do this. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Good luck! -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... You've helped me many , many, many times before, Biff. I appreciate the help thins time; I think this one may be outside the capabilities of Excel's predefined functions. I'm going to the Programming area and look for a UDF, or if I can't find anything, ask for help from the experts there. I'm not very good with developing UDFs. If you can think of something, please post back. Please don't spend a lot of time on it though!! Thanks for everything!! Ryan-- -- RyGuy "T. Valko" wrote: Maybe I'll be able to figure something out if I can see the actual file. Want me to take a look? -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I have to recant my last statement; it is NOT working. Dang!! I could swear it was working a couple hours ago. Is there any way to do what I am trying to do? If I add in a few extra names (Andy, Dave, etc.) I can get the desired results... The thing is, I will only have the name one time in that Column C. Appreciate any help with this. Thanks so much, Ryan--- -- RyGuy "ryguy7272" wrote: Wait, I think I got it! This seems to work: =SUMPRODUCT(--(Sheet2!$A$2:$A$678=Sheet1!$E$2:$E$678),--(Sheet1!$C$2:$C$678=C2),Sheet1!$V$2:$V$678) It's net even an array... -- RyGuy "ryguy7272" wrote: Yes, adding those names (Andy, Dave, etc.) in there was the only way I could get the values I was looking for. However, those names will only apprear once. I thought the Sumproduct, inside the array, picked up all incidences of data (names, numbers, etc.) Is there a workaround? Is there another way to do this? I'm sure there is a VBA solution (Union, probably), but the guy that I am doing this for is definitely VBA savvy. I wanted to give him a function that he will be able to maintain. Any other thoughs or is this a dead end? Thanks, Ryan-- -- RyGuy "Glenn" wrote: ryguy7272 wrote: Here is a sample: http://rs173.rapidshare.com/files/196921830/sample.xls I was trying to get Cell M2 to be 4500. Thanks, Ryan-- You would need "Andy D Fox" in C2 and C3 to get that answer. |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Matches, then Sumproduct of Values
Sheet1 is like this:
ColumnC ColumnM Ray proposed function Tyler proposed function Patrick proposed function Courteney proposed function Dave proposed function Andy proposed function There is a loot of other stuff in that Sheet1; too much to change. I now see why the other attempt failed. Sumproduct is looking for matches in rows! I was hoping you could somehow tell it to look for relationships...now I see how silly that is. I guess this is a dead end. Hopefully someone in the Excel-Programming DG can think of something; I'm out of ideas. -- RyGuy "T. Valko" wrote: P.S. Here's what I'm thinking... If Sheet1 was something like this: .....C.........E......V Andy...Carat...10 ............Carat...20 ............Carat...15 Bill......X.........10 ............X.........10 Lisa....Y.........22 ...........Y.........17 Then we should be able to do this. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Good luck! -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... You've helped me many , many, many times before, Biff. I appreciate the help thins time; I think this one may be outside the capabilities of Excel's predefined functions. I'm going to the Programming area and look for a UDF, or if I can't find anything, ask for help from the experts there. I'm not very good with developing UDFs. If you can think of something, please post back. Please don't spend a lot of time on it though!! Thanks for everything!! Ryan-- -- RyGuy "T. Valko" wrote: Maybe I'll be able to figure something out if I can see the actual file. Want me to take a look? -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I have to recant my last statement; it is NOT working. Dang!! I could swear it was working a couple hours ago. Is there any way to do what I am trying to do? If I add in a few extra names (Andy, Dave, etc.) I can get the desired results... The thing is, I will only have the name one time in that Column C. Appreciate any help with this. Thanks so much, Ryan--- -- RyGuy "ryguy7272" wrote: Wait, I think I got it! This seems to work: =SUMPRODUCT(--(Sheet2!$A$2:$A$678=Sheet1!$E$2:$E$678),--(Sheet1!$C$2:$C$678=C2),Sheet1!$V$2:$V$678) It's net even an array... -- RyGuy "ryguy7272" wrote: Yes, adding those names (Andy, Dave, etc.) in there was the only way I could get the values I was looking for. However, those names will only apprear once. I thought the Sumproduct, inside the array, picked up all incidences of data (names, numbers, etc.) Is there a workaround? Is there another way to do this? I'm sure there is a VBA solution (Union, probably), but the guy that I am doing this for is definitely VBA savvy. I wanted to give him a function that he will be able to maintain. Any other thoughs or is this a dead end? Thanks, Ryan-- -- RyGuy "Glenn" wrote: ryguy7272 wrote: Here is a sample: http://rs173.rapidshare.com/files/196921830/sample.xls I was trying to get Cell M2 to be 4500. Thanks, Ryan-- You would need "Andy D Fox" in C2 and C3 to get that answer. |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Matches, then Sumproduct of Values
Finally got it working and I wanted to share the results with others who
followed this discussion: http://www.microsoft.com/office/comm...sloc=en-us&p=1 Big thanks to... Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com -- RyGuy "T. Valko" wrote: P.S. Here's what I'm thinking... If Sheet1 was something like this: .....C.........E......V Andy...Carat...10 ............Carat...20 ............Carat...15 Bill......X.........10 ............X.........10 Lisa....Y.........22 ...........Y.........17 Then we should be able to do this. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Good luck! -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... You've helped me many , many, many times before, Biff. I appreciate the help thins time; I think this one may be outside the capabilities of Excel's predefined functions. I'm going to the Programming area and look for a UDF, or if I can't find anything, ask for help from the experts there. I'm not very good with developing UDFs. If you can think of something, please post back. Please don't spend a lot of time on it though!! Thanks for everything!! Ryan-- -- RyGuy "T. Valko" wrote: Maybe I'll be able to figure something out if I can see the actual file. Want me to take a look? -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I have to recant my last statement; it is NOT working. Dang!! I could swear it was working a couple hours ago. Is there any way to do what I am trying to do? If I add in a few extra names (Andy, Dave, etc.) I can get the desired results... The thing is, I will only have the name one time in that Column C. Appreciate any help with this. Thanks so much, Ryan--- -- RyGuy "ryguy7272" wrote: Wait, I think I got it! This seems to work: =SUMPRODUCT(--(Sheet2!$A$2:$A$678=Sheet1!$E$2:$E$678),--(Sheet1!$C$2:$C$678=C2),Sheet1!$V$2:$V$678) It's net even an array... -- RyGuy "ryguy7272" wrote: Yes, adding those names (Andy, Dave, etc.) in there was the only way I could get the values I was looking for. However, those names will only apprear once. I thought the Sumproduct, inside the array, picked up all incidences of data (names, numbers, etc.) Is there a workaround? Is there another way to do this? I'm sure there is a VBA solution (Union, probably), but the guy that I am doing this for is definitely VBA savvy. I wanted to give him a function that he will be able to maintain. Any other thoughs or is this a dead end? Thanks, Ryan-- -- RyGuy "Glenn" wrote: ryguy7272 wrote: Here is a sample: http://rs173.rapidshare.com/files/196921830/sample.xls I was trying to get Cell M2 to be 4500. Thanks, Ryan-- You would need "Andy D Fox" in C2 and C3 to get that answer. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count cells w/values in column if the data in column a matches cri | Excel Worksheet Functions | |||
Count values only if matches and return on another worksheet | Excel Worksheet Functions | |||
Add up Values if Find Matches x 2 | Excel Worksheet Functions | |||
how to get values from different sheet when info. matches? | Excel Worksheet Functions | |||
V-lookup and summing values if more than 1 matches criteria | Excel Worksheet Functions |