Two Matches, then Sumproduct of Values

R

ryguy7272

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---
 
T

T. Valko

Maybe this:

=SUMPRODUCT(--(Sheet2!$A$2:$A$678=Sheet1!$E$2:$E$678),--(Sheet1!$C$2:$C$678=C64),Sheet1!$V$2:$V$678)
 
R

ryguy7272

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---
 
T

T. Valko

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.
 
R

ryguy7272

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--
 
R

ryguy7272

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...
 
T

T. Valko

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.

???
 
R

ryguy7272

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---
 
T

T. Valko

Maybe I'll be able to figure something out if I can see the actual file.
Want me to take a look?
 
R

ryguy7272

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--
 
T

T. Valko

Good luck!

--
Biff
Microsoft Excel MVP


ryguy7272 said:
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--
Click to expand...
 
T

T. Valko

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.
 
R

ryguy7272

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count Uniques with a Couple Conditions 2
Need Help with a VBA subroutine 0
Help with sumproduct 2
Data Matching 1
matching full name to 'two column' name using sumproduct 9
UDF; Two Matches, then Sumproduct of Values 0
Count Unique Numbers if Names Match 4
SUMPRODUCT formaula not working 1

Top