UDF for Two Matches and Sumproduct Results

[ryguy7272]

I posted my question here a few days ago, but didn’t get even one response,
so I am re-posting and trying to do a better job of explaining what i want.
I’m pretty sure this is possible, but don’t know exactly how to do it...


Basically, I am trying to come up with a way to match Account Names in
Column A of Sheet2 with Account Names in Column E of Sheet1. If there is a
match there, then match names in Column C of Sheet1 with names in Column C of
Sheet3, and sum the values, in Column V of Sheet1, that match this name!

I uploadad a file to show an example of what I am trying to achieve.
Here is a sample:
http://www.filehosting.org/file/details/13721/yoe2naA4nFLHh1WM/sample.xls

In the sample file, you can see that Tommy is responsible for accounts named
‘BarNone’ and since BarNone has paid 4,500, Tommy should have 4,500 in
revenue under his name. I have a Sumproduct function that does the matching,
but actually only sums the values on the Row where the rep’s name is. This
is just how Sumproduct works; it is what it is. I’m trying to overcome this
limitation by identifying all account names that each rep is responsible for,
and pick up all the revenue for the rep, according to the Account Name.

Again, in the example I posted, Tommy would have 4500 (other calculated
examples shown in Sheet3). I'm sure it can be done with a UDF. I tried
several preset functions, and just couldn’t get Excel to do what I wanted it
to do. Can anyone here understand the logic and supply me with a
user-defined function that will let me do what I want to do? It would
greatly simplify my life for the next several months if I could get this
thing working.

Thanks so much,
Ryan—

 

[ryguy7272]

That other free file sharing site seems to have gone down; I just posted the
file a few minutes ago... Anyway, please see the sample file here:
http://www.savefile.com/files/2004415

Again, I'd greatly appreciate any help with this!!

TIA,
Ryan---

 

[Dick Kusleika]

Basically, I am trying to come up with a way to match Account Names in
Column A of Sheet2 with Account Names in Column E of Sheet1. If there is a
match there, then match names in Column C of Sheet1 with names in Column C of
Sheet3, and sum the values, in Column V of Sheet1, that match this name!

RyGuy: I don't get it. Walk me through the Jeff, Andy, and Timmy
calculations. I don't see how Sheet2 relates to the other sheets. There is
a "match" for every agency, in that every agency on sheet2 exists on sheet1,
but so what? I think if you step through the logic on those three reps, we
can get this figured out.

 

[ryguy7272]

Thanks for looking at this Dick. I would love to get this figured out, both
for myself, and for a colleague who asked me for help with this thing (this
is part three of three; the first two issues have been resolved). So, I’m
helping out a colleague who has a pretty large workbook that does all kinds
of things. All of the columns have data; I just picked out the ones that are
problematic...that’s why it probably looks nutty...it actually does make
sense. It makes perfect sense to me, and I came up with an Access solution
in about 5 minutes, but I’m stumped as to how to do it in Excel. It’s really
a relationship problem; Access is perfect for this kind of thing. I use both
Access and Excel; everyone else in my office uses Excel only. I wouldn’t
expect them to learn Access and this is really the last piece of the puzzle,
so I wouldn’t advocate switching to Access at this point (everything else is
in Excel and works fine). Sorry to circumvent the issue, just trying to give
some background.

Anyway, we are looking at many different advertising agencies on Sheet2 (as
well as many other things but right now I am just interested in the data in
columnA). In Sheet1, we are looking at names of sales reps, which are in
columnA, and advertising agencies, which are in ColumnE (some match the
agencies in Sheet2 and some don’t). In Sheet3, we have the sales reps, which
are in ColumnC (some of the names match those in Sheet1 and some don’t). The
objective is to fine the matching advertising agencies in Sheet2 and Sheet1,
match these to the sales rep names on Sheet1, and then match these sales rep
names with those sales rep names on Sheet3, and when there are matching sales
rep names, sum all values in ColumnV of Sheet3. The sales reps names appear
only once on Sheet3, and this is a major problem (that’s why I can’t use the
built in Excel functions).

Essentially, I want to look at a name on Sheet2, such as ‘BarNone’ (there
will be lots of advertiser names), see if there is a match on Sheet1. I can
tell that there are matches; two advertisers named ‘BarNone’ on Sheet2 and
four advertisers named ‘BarNone’ on Sheet1. On Sheet1, I can tell that Tommy
is the sales rep that handles the ‘BarNone’ account. I then look at ColumnV
of Sheet1, and see that Tommy has 1200 + 1800 + 1500 + 100 in revenue from
‘BarNone’ so I want to sum this and place the result in Sheet3, ColumnM. On
Sheet3, cell M2, I would like to see 4600.

Does that make sense? I believe this is possible, I’ve never done it in
Excel though, and I don’t even really know where to begin. If anyone can see
the logic, as I do, and give me a UDF for this thing, I would be most
appreciative!!

Thanks,
Ryan---

 

[Dick Kusleika]

Thanks for looking at this Dick. I would love to get this figured out, both
for myself, and for a colleague who asked me for help with this thing (this
is part three of three; the first two issues have been resolved). So, I’m
helping out a colleague who has a pretty large workbook that does all kinds
of things. All of the columns have data; I just picked out the ones that are
problematic...that’s why it probably looks nutty...it actually does make
sense. It makes perfect sense to me, and I came up with an Access solution
in about 5 minutes, but I’m stumped as to how to do it in Excel. It’s really
a relationship problem; Access is perfect for this kind of thing. I use both
Access and Excel; everyone else in my office uses Excel only. I wouldn’t
expect them to learn Access and this is really the last piece of the puzzle,
so I wouldn’t advocate switching to Access at this point (everything else is
in Excel and works fine). Sorry to circumvent the issue, just trying to give
some background.

Tell me how you'd do it in Access. What would the SQL statement be?

Anyway, we are looking at many different advertising agencies on Sheet2 (as
well as many other things but right now I am just interested in the data in
columnA). In Sheet1, we are looking at names of sales reps, which are in
columnA, and advertising agencies, which are in ColumnE (some match the
agencies in Sheet2 and some don’t). In Sheet3, we have the sales reps, which
are in ColumnC (some of the names match those in Sheet1 and some don’t). The
objective is to fine the matching advertising agencies in Sheet2 and Sheet1,
match these to the sales rep names on Sheet1, and then match these sales rep
names with those sales rep names on Sheet3, and when there are matching sales
rep names, sum all values in ColumnV of Sheet3. The sales reps names appear
only once on Sheet3, and this is a major problem (that’s why I can’t use the
built in Excel functions).

What constitutes a match? Every Agency on Sheet2 exists on Sheet1, so
they're all matches.

Essentially, I want to look at a name on Sheet2, such as ‘BarNone’ (there
will be lots of advertiser names), see if there is a match on Sheet1. I can
tell that there are matches; two advertisers named ‘BarNone’ on Sheet2 and
four advertisers named ‘BarNone’ on Sheet1. On Sheet1, I can tell that Tommy
is the sales rep that handles the ‘BarNone’ account. I then look at ColumnV
of Sheet1, and see that Tommy has 1200 + 1800 + 1500 + 100 in revenue from
‘BarNone’ so I want to sum this and place the result in Sheet3, ColumnM. On
Sheet3, cell M2, I would like to see 4600.

You can't do Tommy because I can get that answer without using Sheet2 for
Tommy. You have to use the other three guys as examples. For instance, who
is the Rep for Carat? Is it Andy or Jeff and how do you know?

If I sum up all the Tommys, I get 4600. If I sum up the first two Jeffs, I
get 100. If I sum up the one Timmy, I get 400. I can do all those with a
SUMIF that completely ignores Sheet2. Jeff I can't figure out.

 

[ryguy7272]

#1) Here is my SQL:
SELECT Reps.Rep, Company.Company, Sum(Revenue.Revenue) AS SumOfRevenue
FROM Revenue INNER JOIN (Company INNER JOIN Reps ON Company.Company =
Reps.Company) ON Revenue.ID = Company.ID
GROUP BY Reps.Rep, Company.Company;
This is just a paired down version of what I was trying to do; if I ONLY had
to deal with the issue I described. There are many calculations in the
spreadsheet and this is the ONE that hasn't been resolved yet.

#2) The first match would be between ColumnA on Sheet2 and ColumnE on
Sheet1. I am trying to match the advertiser names. The second match would
be ColumnC of Sheet1 and ColumnC of Sheet3. I am now trying to match the rep
names. If there is a match between advertiser names and a match between rep
name, sum all values in ColumnV of Sheet1 and place the sumproduct in ColumnM
of Sheet3.

#3) I suppose the best example would be to look at Timmy. There may be
several advertiser names in ColumnA on Sheet2, such as Carat. There is one
Carat in ColumnE on Sheet1 and the account may be handled by two reps, Andy
and Jeff (typically there is just one rep on the account, but may be two).
Finally, on Sheet3, I'd like to see 200 for Andy and either 300 or 400 for
Jeff. ColumnA of Sheet2 may have 'CardinalSeed' or may not. If ColumnA of
Sheet2 has CardinalSeed, I'd like to see 400 for Jeff and if ColumnA of
Sheet2 does not have CardianlSeed, I'd like to see 300 for Jeff. We will not
have all advertisers all the time and we will not have all reps all the time;
if the names are there I was to test for matches and if the names are not
there, then do nothing. I'm not in the office today, but will be back in on
Wednesday. If this still doesn't make sense, I will look at the Excel file
and try to describe it better. I have a sample file on my home PC and I know
that works essentially the same as the actual file.

Do you think this is possible Dick?

Thanks for everything so far!
Ryan---

 

[ryguy7272]

Sample file here:
http://www.savefile.com/files/2008974


I’m going to try one more time to explain this issue, because at this point
I am pretty confident that at UDF can yield the results I am after…I just
don’t know UDFs well enough to do it myself (I work with Subs much more than
Functions).

In the SampleII workbook, I want to match items in ColumnA on Sheet2 with
items in ColumnE on Sheet1. I can tell that there is a match, which is 'Ads
Inc.'. This is the first condition. Then, I want to match the items on
Sheet1 in ColumnC with items in ColumnC on Summary. I can tell that there is
a match, which is 'Andy'. This is the second condition. Since both
conditions are true, I want to sum all values in ColumnV on Sheet1 that meet
both these conditions (Ads Inc. and Andy) and place the result in ColumnM on
Sheet Summary. The sum is $155,225; cell M58. If one condision was true, or
neither condition was true, ignore, skip, abort, etc. I color-coded the
other two examples; on Excel 2007 now and colors in older versions of Excel
will most likely be different so I won’t try to describe these colors, but
they should match (the colors on Sheet1 and Sheet2 should match) . Does it
make sense? Can this be done with a UDF?

Thanks,
Ryan---

 

[Dick Kusleika]

Sample file here:
http://www.savefile.com/files/2008974


I’m going to try one more time to explain this issue, because at this point
I am pretty confident that at UDF can yield the results I am after…I just
don’t know UDFs well enough to do it myself (I work with Subs much more than
Functions).

In the SampleII workbook, I want to match items in ColumnA on Sheet2 with
items in ColumnE on Sheet1. I can tell that there is a match, which is 'Ads
Inc.'. This is the first condition. Then, I want to match the items on
Sheet1 in ColumnC with items in ColumnC on Summary. I can tell that there is
a match, which is 'Andy'. This is the second condition. Since both
conditions are true, I want to sum all values in ColumnV on Sheet1 that meet
both these conditions (Ads Inc. and Andy) and place the result in ColumnM on
Sheet Summary. The sum is $155,225; cell M58. If one condision was true, or
neither condition was true, ignore, skip, abort, etc. I color-coded the
other two examples; on Excel 2007 now and colors in older versions of Excel
will most likely be different so I won’t try to describe these colors, but
they should match (the colors on Sheet1 and Sheet2 should match) . Does it
make sense? Can this be done with a UDF?

Ryan: Sorry to be so dense, but I think I finally got it now. Put this
formula in Summary!M58

=SUMPRODUCT((Sheet1!$C$2:$C$79=C58)*(NOT(ISNA(MATCH(Sheet1!$E$2:$E$79,Sheet2!$A$2:$A$22,FALSE))))*(Sheet1!$V$2:$V$79))

Here's the breakdown of the three components

(Sheet1!$C$2:$C$79=C58) := Sales Rep is Andy

(NOT(ISNA(MATCH(Sheet1!$E$2:$E$79,Sheet2!$A$2:$A$22,FALSE)))) := Company
exists on Sheet2

(Sheet1!$V$2:$V$79) := Amount to sum

 

[ryguy7272]

Beeeeeee-uuu-teeee-fuullllllll..... You know what Dick, I tried so many
combinations of functions, including a susbtotal for each rep, and
subtracting out amounts that didn't match...all sorts of things. Nothing I
did seemed to work (although I had one false alarm), but you found the secret
formula!!

You definitely earned that MVP title!!

Thanks so much!
Ryan---

 

[Dick Kusleika]

Beeeeeee-uuu-teeee-fuullllllll..... You know what Dick, I tried so many
combinations of functions, including a susbtotal for each rep, and
subtracting out amounts that didn't match...all sorts of things. Nothing I
did seemed to work (although I had one false alarm), but you found the secret
formula!!

You're welcome. It was the SQL statement that finally turned on the light
bulb. I should make a utility that spits out a SUMPRODUCT formula when you
enter a SQL statement. Sounds hard.