You're welcome. Sorry about missing the sheet name, copied and pasted from
sheet I was working on, without engaging brain.

As for "overthinking", I've been known to be guilty of that myself. One
advantage of answering questions rather than asking them is that I'm looking
at the problem in isolation, rather than being in amongst the trees!


"Steve" wrote in message
...
Indeed.....
It does work as you've written it- sans the sheet name.

Thank you Steve.
And as to my "overthinking" it... that's an old habit. I keep trying to
break myself of it, but I can only make small short progressional steps.
You
know-- the old 3 steps forward, and two steps back bit.

Again, thank you. This matter is solved.

"Steve Dunn" wrote:

Hmmm, I think you've been overthinking it. Another way of stating your
formula would be:

=if(a=0, b/c, if(b=0, a/d, a/d+b/c))

which simplifies to:

a/d+b/c

because if a = 0, then a/d = 0 and therefore a/d+b/c would equal 0+b/c or
just b/c. The opposite applies if b = 0, then b/c = 0, therefore
a/d+b/c
is equivalent to a/d+0. Leaving a/d+b/c to give the correct answer where
neither are 0.

So, your formula becomes:

=SUMPRODUCT(($E$4:$E$95&""=$A11&"")*($F$4:$F$95=$C 11)*($B$4:$B$95))/TRIM(LEFT($F$6,4))*$E$6+SUMPRODUCT(($E$97:$E$173&" "=$A11&"")*($F$97:$F$173=$C11)*($B$97:$B$173))/TRIM(LEFT($F$7,4))*$E$7

HTH
Steve D.


"Steve" wrote in message
...
Hi Steve.
Thank you for the response.

1- The &"" in the criteria source is a datatype nullifier, as it was
once
explained to me. I was having troubles with my data in the beginning,
and
after posting here back in August/Sept/November of 2006 on the topic,
Harlan
Grove and Roger Govier explained to me the various ways of doing the
sumproduct. I.e., Sumproduct(--()*()*()); Sumproduct((--)*()*()), and
the
&""
elements. For my purposes the &"" worked more consistently. I'm not
sure
why
the double -- did not work for my purposes, especially since I've used
it
more recently and it has worked.

2- My goal is to check if there are two sources, or one. Each set has
its
own ratio that occurs when I want to perform a secondary operation on
them.
As such, I have to delineate between them, then perform the secondary
operation, and add those two results together.
If there are only values in dataset A, I perform the ratio operation on
dataset A. If dataset B, perform operation on B.
So far it appears that I was able to come up with a solution, but boy
is
it
u-g-l-y.

After I'd posted this, I talked with a colleague to "clear out my
cobwebs"
and came up with the following.

=IF((SUMPRODUCT((APN!$E$4:$E$95&""=$A11&"")*(APN!$ F$4:$F$95=$C11)*(APN!$B$4:$B$95))<0),IF((SUMPRODU CT((APN!$E$97:$E$173&""=$A11&"")*(APN!$F$97:$F$173 =$C11)*(APN!$B$97:$B$173))<0),(SUMPRODUCT((APN!$E $4:$E$95&""=$A11&"")*(APN!$F$4:$F$95=$C11)*(APN!$B $4:$B$95))/TRIM(LEFT($F$6,4))*$E$6+SUMPRODUCT((APN!$E$97:$E$1 73&""=$A11&"")*(APN!$F$97:$F$173=$C11)*(APN!$B$97: $B$173))/TRIM(LEFT($F$7,4))*$E$7),(SUMPRODUCT((APN!$E$4:$E$ 95&""=$A11&"")*(APN!$F$4:$F$95=$C11)*(APN!$B$4:$B$ 95))/TRIM(LEFT($F$6,4))*$E$6)),(SUMPRODUCT((APN!$E$97:$ E$173&""=$A11&"")*(APN!$F$97:$F$173=$C11)*(APN!$B$ 97:$B$173))/TRIM(LEFT($F$7,4))*$E$7))

I had to start with a compound/nested IF, and then insert the
sumproduct
eq's accordingly.
I.e.,
IF((),IF((),(),()),())

Where
IF((TestA<0),IF((TestB<0),(True ResponseA + True ResponseB),(False
ResponseA)),(FalseResponseB))

While it appears to work, I'd like to reduce its complexity-- and I
don't
use the name manager because of prior issues faced with it.

Hope this helps clarify.

Thanks again for your response, and helps.

"Steve Dunn" wrote:

Hi Steve,

1. Why (APN!$E$4:$E$95&""=$A11&"") rather than (APN!$E$4:$E$95=$A11)
?

2. What kind of result do you want when both data sets have matching
values
within? Do you want the results in two seperate cells, added together
in
one cell, or does one result take priority over the other (and how is
that
priority decided)?




"Steve" wrote in message
...
Howdee all.
Hope everyone's afternoon, evening, etc... are going well.

I have a dual worksheet function that requires something more
compound
that
I've used before.
Thus far, I've tried using an if equation, that looks through a
dataset
with
a sumproduct, and if the dataset matches, I perform one equation,
and
if
not,
another.
E.g.,

=IF(SUMPRODUCT((APN!$E$4:$E$95&""=$A11&"")*(APN!$F $4:$F$95=$C11)*(APN!$B$4:$B$95))<0,SUMPRODUCT((AP N!$E$4:$E$95&""=$A11&"")*(APN!$F$4:$F$95=$C11)*(AP N!$B$4:$B$95))/TRIM(LEFT($F$6,4))*$E$6,SUMPRODUCT((APN!$E$97:$E$1 73&""=$A11&"")*(APN!$F$97:$F$173=$C11)*(APN!$B$97: $B$173))/TRIM(LEFT($F$7,4))*$E$7)

The issue that I've found is that there is a handful of datum that
has
values in both ranges-- 4 through 95, AND 97 through 173.
This would require me to have both equations.

What would be a better way to handle this?

Thank you.