How can I SUM values only when specific text in another cell in the row is met?

[SublimeLB]

Here's my dilemma...
I'm creating 10 'categories' for Column "C" that I plan on using drop-downs to select from. Column "H" has a value tied to "C". What I want to do, is have Excel SUM all of the values in Column "H" where the Category in "C" is a specific text value (e.g. say a category is "hardware", I want a cell with a formula that sums all of the values in "H" where "C" is "hardware".)

Any help from the experts would be greatly appreciated!

[NBVC]

Welcome to our forum,,

Please take the time to read our forum rules. You need to adjust your title to something more descriptive.


Thank you and enjoy the forum.

[SublimeLB]

Welcome to our forum,,

Please take the time to read our forum rules. You need to adjust your title to something more descriptive.


Thank you and enjoy the forum.

Sure thing. My apologies.



edit: I can't seem to find where I can edit the thread title.
edit #2: nevermind, I see it in the forum rules :-X

[SublimeLB]

Ok, thread title edited, though I'm not sure my question made any sense.

[NBVC]

Here's my dilemma...
I'm creating 10 'categories' for Column "C" that I plan on using drop-downs to select from. Column "H" has a value tied to "C". What I want to do, is have Excel SUM all of the values in Column "H" where the Category in "C" is a specific text value (e.g. say a category is "hardware", I want a cell with a formula that sums all of the values in "H" where "C" is "hardware".)

Any help from the experts would be greatly appreciated!

The Sumif() function should be able to do this.

Have a look at that function in Excel Help. If you need further assistance please do not hesitate to post back...

[SublimeLB]

The Sumif() function should be able to do this.

Have a look at that function in Excel Help. If you need further assistance please do not hesitate to post back...

I'll start there, thanks.

[SublimeLB]

Oh man, that was a piece of cake, thanks... edit: Crap, except I just realized I need the average, not sum, and it appears my old Excel 2003 doesn't have AverageIf (2007 does). Any suggestions?

[SublimeLB]

I tried =AVERAGE(IF(C2:C8="Hardware",H2:H8))

That was pretty comical. Still no idea how to do this without using AverageIf

[NBVC]

In 2003, there is no Averageif() function... the way you wrote it is the correct way to find the conditional Average.... but this is now an array formula and you must confirm it with CTRL+SHIFT+ENTER not just Enter. You will note{ } brackets appear around the formula.

[Jena526]

I am trying to do the exact same thing but across different spreadsheets in the same workbook. I have 5 tabs:

1Qtr, 2Qtr, 3Qtr, 4Qtr and FY11 Totals

I want the sum to appear on the FY11 Totals spreadsheet. The number is always in column I and the text is always in column J. So the formula I am using is
=SUMIF('1Qtr'!I3:'2Qtr'!I3:'3Qtr'!I3:'4Qtr'!I3,"text",'1Qtr'!J3:'2Qtr'!J3:'3Qtr'!J3:'4Qtr'!J3)

Can someone please tell me what I am doing wrong?

Thanks so much!
Jena

[NBVC]

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.