Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count cells w/values in column if the data in column a matches cri mdcgpw Excel Worksheet Functions 4 January 13th 09 12:55 AM
Count values only if matches and return on another worksheet candacer Excel Worksheet Functions 3 May 27th 08 09:49 PM
Add up Values if Find Matches x 2 Sean Excel Worksheet Functions 9 November 16th 07 01:21 PM
how to get values from different sheet when info. matches? juanpauk Excel Worksheet Functions 3 March 2nd 06 08:02 PM
V-lookup and summing values if more than 1 matches criteria holcay Excel Worksheet Functions 3 February 6th 06 04:52 PM


All times are GMT +1. The time now is 10:57 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"