Conditional formulas with sum and if

[jackie]

Please help me this formula is driving me nuts. I am trying to add multiple
ranges of fields, then taking the total and multiply by tenant square
footage, then divide by gross leasable area, then take that total and
multiply by an admin fee......NOT DONE YET.....then take that total and check
it with an if statement....is this total <K55 if so enter the total, if false
enter the value from field K55. So far this has stumped multiple IT
personnel...PLEASE HELP! Here's an example of the formula:

=-(sum(AC$215:AC$250+AC$252+AC$255)+(SUM B20:25+B30)*G55/J55*M55/12),
if(<k55,[sum of above],[k55])

Thanks,

 

[Biff]

Hi!

Try this:

=IF(SUM(AC215:AC250,AC252,AC255,B20:B25,B30)*G55/J55*(M55/12)<K55,K55,SUM(AC215:AC250,AC252,AC255,B20:B25,B30)*G55/J55*(M55/12))

OR, use an intermediate cell to hold:

A1 =SUM(AC215:AC250,AC252,AC255,B20:B25,B30)

Then:

=IF(A1*G55/J55*(M55/12)<K55,K55,A1*G55/J55*(M55/12))

Biff

 

[Biff]

Ooops!

I have it backwards. Should be:

=IF(SUM(AC215:AC250,AC252,AC255,B20:B25,B30)*G55/J55*(M55/12)<K55,SUM(AC215:AC250,AC252,AC255,B20:B25,B30)*G55/J55*(M55/12),K55)

And if you use the intermediate cell (A1 to hold the SUM portion):

=IF(A1*G55/J55*(M55/12)<K55,A1*G55/J55*(M55/12),K55)

Biff

Hi!

Try this:

=IF(SUM(AC215:AC250,AC252,AC255,B20:B25,B30)*G55/J55*(M55/12)<K55,K55,SUM(AC215:AC250,AC252,AC255,B20:B25,B30)*G55/J55*(M55/12))

OR, use an intermediate cell to hold:

A1 =SUM(AC215:AC250,AC252,AC255,B20:B25,B30)

Then:

=IF(A1*G55/J55*(M55/12)<K55,K55,A1*G55/J55*(M55/12))

Biff

Please help me this formula is driving me nuts. I am trying to add
multiple
ranges of fields, then taking the total and multiply by tenant square
footage, then divide by gross leasable area, then take that total and
multiply by an admin fee......NOT DONE YET.....then take that total and
check
it with an if statement....is this total <K55 if so enter the total, if
false
enter the value from field K55. So far this has stumped multiple IT
personnel...PLEASE HELP! Here's an example of the formula:

=-(sum(AC$215:AC$250+AC$252+AC$255)+(SUM B20:25+B30)*G55/J55*M55/12),
if(<k55,[sum of above],[k55])

Thanks,

 

[jackie]

Biff thank you for your reply. I am still have some trouble with getting a
result in the cell. Here is my formula:

=IF(SUM($AC$176:$AC$231,$AC$232,$AC$233,$AC$252,$AC$240:$AC$251)*$F56/$G56*($M56/12)<K56,SUM($AC$176:$AC$231,$AC$232,$AC$233,$AC$252,$AC$240:$AC$251)*$F56/$G56*($M56/12),K56)=IF(A1<K$56,A1,K$56)

a1 has a temporary sum of 31
k56 has an amount of $300
My answer at this moment is 0.....what am I missing?

Thanks again.

Hi!

Try this:

=IF(SUM(AC215:AC250,AC252,AC255,B20:B25,B30)*G55/J55*(M55/12)<K55,K55,SUM(AC215:AC250,AC252,AC255,B20:B25,B30)*G55/J55*(M55/12))

OR, use an intermediate cell to hold:

A1 =SUM(AC215:AC250,AC252,AC255,B20:B25,B30)

Then:

=IF(A1*G55/J55*(M55/12)<K55,K55,A1*G55/J55*(M55/12))

Biff

Please help me this formula is driving me nuts. I am trying to add
multiple
ranges of fields, then taking the total and multiply by tenant square
footage, then divide by gross leasable area, then take that total and
multiply by an admin fee......NOT DONE YET.....then take that total and
check
it with an if statement....is this total <K55 if so enter the total, if
false
enter the value from field K55. So far this has stumped multiple IT
personnel...PLEASE HELP! Here's an example of the formula:

=-(sum(AC$215:AC$250+AC$252+AC$255)+(SUM B20:25+B30)*G55/J55*M55/12),
if(<k55,[sum of above],[k55])

Thanks,

 

[Biff]

Hi!

My answer at this moment is 0.....what am I missing?

Hmmm.....

Well, that's a fairly straight forward formula.

Are you sure these cells have values in them greater than zero: F56,G56, M56

SUM($AC$176:$AC$231,$AC$232,$AC$233,$AC$252,$AC$240:$AC$251)

You can shorten that a little:

SUM($AC$176:$AC$233,$AC$240:$AC$252)

If you can't figure it out you can send me a copy of the file and I'll take
a look. Just let me know how to contact you.

Biff

Biff thank you for your reply. I am still have some trouble with getting
a
result in the cell. Here is my formula:

=IF(SUM($AC$176:$AC$231,$AC$232,$AC$233,$AC$252,$AC$240:$AC$251)*$F56/$G56*($M56/12)<K56,SUM($AC$176:$AC$231,$AC$232,$AC$233,$AC$252,$AC$240:$AC$251)*$F56/$G56*($M56/12),K56)=IF(A1<K$56,A1,K$56)

a1 has a temporary sum of 31
k56 has an amount of $300
My answer at this moment is 0.....what am I missing?

Thanks again.

Hi!

Try this:

=IF(SUM(AC215:AC250,AC252,AC255,B20:B25,B30)*G55/J55*(M55/12)<K55,K55,SUM(AC215:AC250,AC252,AC255,B20:B25,B30)*G55/J55*(M55/12))

OR, use an intermediate cell to hold:

A1 =SUM(AC215:AC250,AC252,AC255,B20:B25,B30)

Then:

=IF(A1*G55/J55*(M55/12)<K55,K55,A1*G55/J55*(M55/12))

Biff

Please help me this formula is driving me nuts. I am trying to add
multiple
ranges of fields, then taking the total and multiply by tenant square
footage, then divide by gross leasable area, then take that total and
multiply by an admin fee......NOT DONE YET.....then take that total and
check
it with an if statement....is this total <K55 if so enter the total, if
false
enter the value from field K55. So far this has stumped multiple IT
personnel...PLEASE HELP! Here's an example of the formula:

=-(sum(AC$215:AC$250+AC$252+AC$255)+(SUM B20:25+B30)*G55/J55*M55/12),
if(<k55,[sum of above],[k55])

Thanks,