Ron: If D1 is left blank I then wanted a return of "", sorry I didn't think
of it! Your solution gave me a hugh negative answer when there was data, and
"#VALUE" if there was no data.

"Ron Rosenfeld" wrote:

On Sat, 25 Oct 2008 20:23:01 -0700, DavidT
wrote:

Tried every combination I could think of, here's what I need for for a
solution for ONE cell:

If D1=2 and D3 is <14 then D5=0 , If D1=2 and D3 is 14 then D3-14*F1 OR
If D1=3 and D3 is <16 then D5=0 , If D1=3 and D3 is 16 then D3-16*F1 OR
If D1=4 and D3 is <17 then D5=0 , If D1=4 and D3 is 17 then D3-17*F1

How can I get this into a single formula for an answer in one cell?


You don't define what you want as a result should D1 not equal 2, 3 or 4; nor
do you define what you want as a result should D3 equal 14, 16, or 17.

Assuming in the first case that you will want to return a zero, then either:

=SUMPRODUCT((D1={2,3,4})*(D3={14,16,17})*(D3-{14,16,17}*F1))

or

=SUMPRODUCT((D1={2,3,4})*(D3{14,16,17})*(D3-{14,16,17}*F1))

depending on what you want to happen if D3 is exactly 14,16, or 17

might work for you.
--ron