# 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

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

#### Zack Barresse

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

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

• 14 KB Views: 54

#### bomb #21

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.

#### Zack Barresse

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

• 15.4 KB Views: 55

#### PennyPower

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.

As Seen On