Excel Formula Help-If Statement

[acool]

Hi Everyone,

I am currently experiencing an issue with an if-statement formula that I am working on and was hoping to receive some help. In the current Data set shown, I am calculating a New Fee in Column C using the formula in the formula bar. After calculating this fee I am then multiplying this number by the number of returns per month to get the value in Column D. I am them summing all of these values in column D and multiplying by 12 to get the total value for the Year (D19). I was was hoping someone could help me create a formula which calculates the value for the year (D19) without using the values in Column D. I would somehow like to create a Sum product formula using Columns A, B and the formula in the formula bar to arrive at the value in D19. I'm trying to eliminate the Columns C & D and trying to fit everything into one formula. Any help would be greatly appreciated! Thank You!

 

[acool]

I can't anderstand how, in cell D2, you have 1,5, if you're multiplying B2*C2... B2 is 0, so, D2 should be 0?

Decimals colapsed, rounded, or not...the result should be 0?

These are the true values here:

 

[awoohaw]

For some reason, when I enter the formula above I am get a value of 202 as opposed to 2426.86. Not sure if I am doing something wrong here

I don't know. Take out all rounding, AND formatting AND move decimal to 4 or 5 spaces; and compare number.

 

[Fluff]

Maybe

=SUMPRODUCT(B2:B18,IF(A2:A18*E1>5,5,A2:A18*E1))*12

But that doesn't match your result, which is why I asked for your data, not an image.

 

[awoohaw]

For some reason, when I enter the formula above I am get a value of 202 as opposed to 2426.86. Not sure if I am doing something wrong here

i left out the multiply by 12. and with different rounding routines you may get a difference.

=12*SUMPRODUCT(($B$2:$B$8),ROUND(IF(($A2:$A8)*$E$1>5,5,($A2:$A8)*E$1),2))

 

[acool]

i left out the multiply by 12. and with different rounding routines you may get a difference.

=12*SUMPRODUCT(($B$2:$B$8),ROUND(IF(($A2:$A8)*$E$1>5,5,($A2:$A8)*E$1),2))

This Worked! Thank you Everyone for all of your help!

 

[Fluff]

Glad we could help & thanks for the feedback.