Forum Discussion

Copper Contributor

Jan 02, 2018

Solved

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 o...

Formulas & Functions

Need Help

IngeborgHawighorst
Jan 02, 2018
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.

Carol Van Valkenburg

Copper Contributor

Jan 09, 2018

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),"")

IngeborgHawighorst

MVP

Jan 10, 2018

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.