Questions

Formula help for Spreadsheet

Tags: Windows, Software
+
0 Votes

Formula help for Spreadsheet

TrundleButt
Hello, I've just recently started using spreadsheets to help manage my budget and sometimes I have a time when the amount isn't actually used, is there a way to check if a cell has a certain value to then use the number from another to add it to the calculation.

=SUM(B2,B3,B4,B5,B6) gives me the total for all of these but B4 and B6 aren't always used, so what i'm trying to accomplish is: if C2="YES", C3="YES", C4="NO", C5="YES", C6="No"
then =SUM(B2,B3,B5)

does that make sense to anyone? lol

Member Answers

    • +
      0 Votes
      dogknees

      If you want to have the second sum when ALL the conditions are true.

      =IF(AND(C2="YES", C3="YES", C4="NO", C5="YES", C6="No"),SUM(B2,B3,B5),SUM(B2,B3,B4,B5,B6))

      If you want the second sum when ANY of the conditions are true.

      =IF(OR(C2="YES", C3="YES", C4="NO", C5="YES", C6="No"),SUM(B2,B3,B5),SUM(B2,B3,B4,B5,B6))

      Hope that helps

      dogknees

      +
      0 Votes
      TrundleButt

      It works for exactly what i posted but I was hoping for some freedom with it, It's basically some bills are monthly some are weekly etc and I'm trying to calculate it by weekly, so 3 times some are ignored and some aren't so could you explain it a little so maybe I could expand/modify it to my own means? Thanks

      +
      0 Votes
      tmalo627

      You can either define your variable nested inside an =if formula, or use a cell off to the side of your table to define it. What type of criteria are you wanting the fluidity with?

    • +
      0 Votes
      dogknees

      If you want to have the second sum when ALL the conditions are true.

      =IF(AND(C2="YES", C3="YES", C4="NO", C5="YES", C6="No"),SUM(B2,B3,B5),SUM(B2,B3,B4,B5,B6))

      If you want the second sum when ANY of the conditions are true.

      =IF(OR(C2="YES", C3="YES", C4="NO", C5="YES", C6="No"),SUM(B2,B3,B5),SUM(B2,B3,B4,B5,B6))

      Hope that helps

      dogknees

      +
      0 Votes
      TrundleButt

      It works for exactly what i posted but I was hoping for some freedom with it, It's basically some bills are monthly some are weekly etc and I'm trying to calculate it by weekly, so 3 times some are ignored and some aren't so could you explain it a little so maybe I could expand/modify it to my own means? Thanks

      +
      0 Votes
      tmalo627

      You can either define your variable nested inside an =if formula, or use a cell off to the side of your table to define it. What type of criteria are you wanting the fluidity with?