| 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. |
| 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 | |||
Hybrid Mode
