Excel Question

Discussion in 'Windows OS and Software' started by Clutch, Mar 10, 2013.

Thread Status:
Not open for further replies.
  1. Clutch

    Clutch cute and cuddly boys

    Reputations:
    1,053
    Messages:
    2,468
    Likes Received:
    25
    Trophy Points:
    66
    I am working on making a spread sheet for my personal budget.

    Attached is a clipping of my set up.


    Now I would like to have a column on the side for the summation of certain purchase,
    For example if "For" has "Gas" and the amount in the next column how can I write an equation to give me the total of the gas for that month or selection of cells?

    So on the side I can have a cell for how much money I spent on "Gas" in one month.

    And likewise be able to substitute the month for any month and the item (Gas, Steam, Feed, Rent, Savings, ect). Something that automatically finds all the amounts for that particular item in a specific range of time.


    I am thinking an If statement of some kind but cant really think of done that works in that way...

    Any thoughts? Or clarification?

    I am thinking something along the line of IF 2013 March and Wages then sum these cells next to the ones that meets that criteria...
     

    Attached Files:

  2. Clutch

    Clutch cute and cuddly boys

    Reputations:
    1,053
    Messages:
    2,468
    Likes Received:
    25
    Trophy Points:
    66
    I think I found how I am going to do it even though it is not very elegant.

    And that is to make a if statement IF(AND(D4="March",E4=2013,F4="Wages"),cell with money,0) and then drag it to the bottom. Then hide the column. I was thinking about doing the calculations on a second sheet but I did not like the flipping back and forth...
     
  3. yalcin19

    yalcin19 Notebook Consultant

    Reputations:
    66
    Messages:
    138
    Likes Received:
    0
    Trophy Points:
    30
    you can use filter data (select the whole table then, Data > Filter > auto filter)
    Then if you click on the drop down menu for the specific column and select gas.
    you can highlight the whole column and see the sum on the bottom right corner.

    or use the following function
    =SUMIF(C7:C12,"gas",D7 : D12)
     
  4. Clutch

    Clutch cute and cuddly boys

    Reputations:
    1,053
    Messages:
    2,468
    Likes Received:
    25
    Trophy Points:
    66
    But then wouldn't it do all the D7:D14 if all the C7:C12 were "gas" What if say only C9 and C11 were gas so I would only want to sum D9 and D11
     
  5. yalcin19

    yalcin19 Notebook Consultant

    Reputations:
    66
    Messages:
    138
    Likes Received:
    0
    Trophy Points:
    30
    Untitled.jpg
    that does exactly what you wanna do.
    it will only sum values on rows containing gas.

    =SUMIF(C7:C12,"gas",D7 : D12)
    if the value on column C is gas then it will sum the value on column D else not.
    gas 5
    water 3
    elec 65
    gas 79
    water 80
    water 85
    gas 21
    elec 15
    105 =SUMIF(E8:E15,"gas",F8:F15)
     
  6. Clutch

    Clutch cute and cuddly boys

    Reputations:
    1,053
    Messages:
    2,468
    Likes Received:
    25
    Trophy Points:
    66
    Brillient :D
     
Loading...
Thread Status:
Not open for further replies.

Share This Page