1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel Formula help

Discussion in 'Business Applications' started by PennyPower, Nov 16, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. PennyPower

    PennyPower Thread Starter

    Joined:
    Nov 16, 2011
    Messages:
    3
    Hello,

    I'm looking for a little help with an Excel formula. It is for a study plan I am creating. I think it should be an SUMIF something, I just can't quite figure it out.

    I have my list of subjects in column C, a few other columns, a notional study time in F and my actual study time in G.

    Further down I am doing a daily update on which subject I spent my time on in column C (using a drop down list) and the hours I spent on it in D.

    So the formula I'm trying to create is for my actual study time, in column G. What I want it to do is, if any of my range of data from the daily updates equals the Subject then it should display the total number of hours I spent on it, which have been entered into column D, and this should carry on down my spreadsheet for all 8 subjects.

    I hope this makes sense and is possible! Any help would be much appreciated :)

    Penny x
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    HI there Penny, welcome to the board!

    Yes, this is possible. A couple of ways we could go about it. 1) Use a PivotTable (PT). 2) Use a formula. The PT is nice because it does all the work for you. Downside is their a little more difficult to format, but they've come a long way, especially since 2007 when they got a 'face lift' and were redesigned. For your formula, it would be a SUMIF(), yes. That is if you only have one condition. For multiple conditions you could either use SUMIFS() or a SUMPRODUCT() formula.

    Which version of Excel are you using?

    Assuming your range data starts at the top, header in row 1, actual data starts in row 2, and your data is not put in a Table (which I would recommend doing, it makes this so much easier), you could use some variation of this, assuming your value to check subjects against is housed in K2, and your range of data goes to row 10 (adjust as necessary)...

    Code:
    =SUMIF(C2:C10,K2,G2:G10)
    Let us know if you have any questions.

    HTH
     
  3. PennyPower

    PennyPower Thread Starter

    Joined:
    Nov 16, 2011
    Messages:
    3
    Hi Zack, thanks for your reply!

    I'm using Excel 2010. I've attached the file so it might make it easier to understand what I'm trying to do. So I still have a diary of how I spent my time but a total of the hours spent on each subject at the top what I'm looking to do is, if I select a subject between C16:C165 and type the hours in the cell to it's right, I want it to automatically update the cells from G4:G12 with the total hours for each particular subject without having to manually add the hours on each subject. but because there's multiple subject I'm finding it tricky to get it right?

    Penny x
     

    Attached Files:

  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    1. Select G4:G12.

    2. Paste this into the formula bar:

    =SUMIF($C$16:$C$180,C4,$D$16:$D$180)

    3. Press CTRL+Enter.
     
  5. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Since you're using 2010, you can use Tables. If you made your two sets of data into Tables, you could use the following formula...

    Code:
    =SUMIF(tblData[Subject],[@Subject],tblData[Hours])
    This would require that you named the lower table tblData (or amend the formula accordingly). I've changed your file and saved another copy with this already done, so you could see how it would work. You also get a fairly good amount of predefined formats for Tables with Excel (2007 and beyond).

    HTH
     

    Attached Files:

  6. PennyPower

    PennyPower Thread Starter

    Joined:
    Nov 16, 2011
    Messages:
    3
    Ah that's it! I get it now. I just couldn't quite get it right before.

    Thanks so much for your help! :)

    Penny x
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1027114

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice