Can Sumproduct do this?

[dexman]

Here is the problem.

I have a multi-tabbed sheet where the last tab contains lease factors
such as:

..0297
..02417
..02034

in a single column. Then there is a second column for a different type
of lease.

I have designed a sheet with 2 drop downs. Drop down "A" has 2 entries
in it returning a 1 or a 2 depending on the choice, and then Drop down
"B" has 7 entries in it for the various term lengths.

In plain english here is what I need to do:

IF Drop Down "A" is equal to 1 AND Drop Down "B" is equal to 1 AND
(some cell) is Greater Than 2999.99 THEN multiply (some cell) by the
appropriate rate on the Factors Page.

Wow. The IF function does not have enough depth to look at all of the
possibilities and I could not find a case function and I dont know VBA

Can anyone help me at all?

 

[Guest]

That sounds more like a vlookup to me than a sumproduct. the big question is
how do you find the factor on the rates page???

=A3 * if(and(A1 = 1, A2 = 1, A3>2999.99), vlookup(this, Inthat, returnthat,
false), 0)

 

[dexman]

The problem with that is that the table does not have the value to
return. The value to return is what I need to calculate a certain way
based on criteria.

Here are the possibilities in list format

This is the first set of possible choices, they are in a drop down and
return the values of 1,2,3,4,5,6,7 depending on the users choice.

TermInMonths = (12,24,36,39,48,60,63)

The second set of possible choices, which are in a seperate drop down
return the values of 1,2 depending on the users choice

LeaseType = (Fair Market Value, 1.00 Buy Out)

So as you can see you can have a lease of 1 of 2 types and it can then
be a term of 12,24,36,39,48,60 or 63 months in length

There is a table that holds the rates based on the amount being leased.
There is a seperate table of rates for leases that are under $3000.00
for both lease types and a seperate table of rates for leases $3000.00
and up for both lease types.

In summary I need to determine lease type, term length and cost
threshold, get the apropriate rate based on that criteria and then
multiply that rate by the cost of equipment.

I hope this helps everyone understand what I am trying to accomplish,
someone rescue my brain please!!

Thanks in advance!