Forum Discussion
Sumif formula need help
I use an excel spreadsheet to enter payroll data for our company. I have one cell that calculates our 401K match (which we pay 100% up to 3% and 50% from 3% to 5%). I would like to have this cell only calculate an amount if the cell that contains an employees payroll amount greater than nothing.
I am trying to enter a formula that will process a formula if a certain cell has a value of more than .00.
For example if cell b14 has a value of more than .00 I want excel to enter a formula. I can do this if I enter the formula into a cell and then reference that cell. Example =sumif(b14,">.00",d20). It give me the amount of cell d20 (which has the formula). I would like to do this completely in cell d20. I have tried: =sumif(b14,">.00)((B14*0.03)+(((B14*0.05)-(B14*0.03))*0.5) I have tried different variations of this formula and I keep getting an error message.
Can anyone help. Thanks
Hello,
change the formula in cell D20. I'm not quite clear what that formula is, but it seems to be something like
=(B14*0.03)+(((B14*0.05)-(B14*0.03))*0.5)
If you want this formula to calculate only when B14 is greater than 0, then wrap that formula into an IF() function, not a SumIF. IF() has the syntax IF(condition, do when true, do when false)
=if(B14>0,(B14*0.03)+(((B14*0.05)-(B14*0.03))*0.5),"")
In words: if B14 is greater than 0 then do the calculation, else return a blank.
Let me know if that helps.
Hello,
change the formula in cell D20. I'm not quite clear what that formula is, but it seems to be something like
=(B14*0.03)+(((B14*0.05)-(B14*0.03))*0.5)
If you want this formula to calculate only when B14 is greater than 0, then wrap that formula into an IF() function, not a SumIF. IF() has the syntax IF(condition, do when true, do when false)
=if(B14>0,(B14*0.03)+(((B14*0.05)-(B14*0.03))*0.5),"")
In words: if B14 is greater than 0 then do the calculation, else return a blank.
Let me know if that helps.
Thanks. That worked. Question, what are the "" for at the end of the equation?
The two double quotes are an empty text. They sit in the part for "do when false". So, if the condition is not met, a blank is returned.
Hi Ingeborg,
Have another question. The reference of CV19 represents a negative number. It is the amount of deduction from an employee's check. The rest represents company match to this deduction.
1. Is this the correct way to enter this?
2. Also is there an alternative way to enter if say CV19 had an entry, no matter what the amount? I ask this in case I have a similar equation with different criteria.
Example:
=IF(CV19<0,ROUNDDOWN(CS14*0.03,2)+ROUNDDOWN(((CS14*0.05)-(CS14*0.03))*0.5,2),"")
Sorry, that sounds a bit confusing.
Can you explain in words what you want to calculate? You need to explain concepts like "company match to this deduction". Give a few examples with input and result and explain the logic.
After that, we can work on a formula to calculated that.
