Forum Discussion

Diane Dambrosio's avatar
Diane Dambrosio
Copper Contributor
Nov 19, 2017

IF Function

I'm having problems figuring out an "IF" formula, I'm wondering if anyone can help me out.  The question is as follows:  If the Repeat? value is "YES", calculate the amount paid by subtracting 5 from...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 20, 2017

Or the same

=A2-(B2="Yes")*5
Rach31's avatar
Rach31
Copper Contributor
Apr 26, 2021

SergeiBaklan 

 

I know it's been years, but your formula still holds 🙂

What if I want multiple cells subtracted from one particular cell, so:

 

=A1-(B1="yes")*5

=A1-(B2="yes")*3

=A1-(B3="yes")*8

 

Is there a way to make this one formula?

 

Thanks!

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 26, 2021

    Rach31 

    Perhaps you mean

    =A1-(B1="yes")*5 - (B2="yes")*3 - (B3="yes")*8

    • Rach31's avatar
      Rach31
      Copper Contributor
      Apr 27, 2021

      Wow, thanks! F1) for your quick reply and 2) for another helpful formula.

      Can I ask you one more thing?

      In A1 I have a sum for numbers in B1 to B10 (which is my monthly bills)
      In C1 to C10 I have made a list with paid "Yes" or "No".

      Some of the bills have to be paid every 3 months (instead of per month).
      If for example B4 is to be paid quarterly and I list C4 (Paid?) as "No", is there a way for B4 not to be added in the sum in A1?

       

      What I have now (sorry, it's in Dutch) is:

      =SOM(N10:N31)

       

      I would like it to be:

      =SOM(N10:N31) - N15 if M15 is "No" and - N18 if M18 is "No"

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Apr 27, 2021

        Rach31 

        That's better to discuss with the sample file. In general, to sum from B1:B10 only values against which in C1:C10 is "No" could be

        =SUMIF(C1:C10,"no",B1:B10)

Resources