MrExcel Publishing
Your One Stop for Excel Tips & Solutions

summing =IF...... amounts


Posted by Noir on December 13, 2001 6:09 AM

My bank allows me to make up to 8 (1-800) calls to check the status of my accounts. As of the 9th call, my bank hits me with a $3.00 service fee.

On my bank spreadsheet, my formula
=IF(H21>8,"$3.00","$0.00") gives me a $3.00 fee amount in cell K24 but, the $3.00 is not being included in my =SUM(K1:K100) command. How can i make sure that the $3.00 fee is included in my sum?

Thanks,
Tony


Posted by Juan Pablo G. on December 13, 2001 6:13 AM

=(H21>8)*3 and format as currency. [NT]

Posted by Bruno on December 13, 2001 6:16 AM

Try this :
=IF(H21>8, 3.00, 0.00)

Posted by Noir on December 13, 2001 6:18 AM

Re: =(H21>8)*3 and format as currency. [NT]

:Worked like a charm, Thanks Juan!!

Posted by Noir on December 13, 2001 9:42 AM

Juan, one final question!!!

:I would also like to have the formula add an additional $.50 for every call above 8 (eg:at call 9, add $.50 to K24, at call 10 add $.50 to K24, etc). Can this be done?

Thanks.


:Worked like a charm, Thanks Juan!!

Posted by Russell Hauf on December 13, 2001 10:05 AM

Try: =(H28>8)*(3+(H28-8)*0.5)....(nt)

Thanks.

Posted by Noir on December 13, 2001 11:00 AM

Thanks Russell!! (NT)