Solved: Excel Formula help

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 
Joined
Jul 25, 2004
Messages
5,458
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
 

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
 

Attachments

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.
 
Joined
Jul 25, 2004
Messages
5,458
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
 

Attachments

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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top