Forum Discussion

Carol Van Valkenburg's avatar
Carol Van Valkenburg
Copper Contributor
Jan 02, 2018

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's avatar
    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. 

     

     

IngeborgHawighorst's avatar
IngeborgHawighorst
MVP
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's avatar
    Carol Van Valkenburg
    Copper Contributor
    Jan 03, 2018

     

    Thanks.  That worked.  Question, what are the "" for at the end of the equation?  

     

     

    • IngeborgHawighorst's avatar
      IngeborgHawighorst
      MVP
      Jan 03, 2018

      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. 

  • Carol Van Valkenburg's avatar
    Carol Van Valkenburg
    Copper Contributor
    Jan 10, 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's avatar
      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. 

Resources