Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Possible Duplicate:
Sum Values in One Range Based on Criteria in Another

I have the following Excel document, which tracks hours needed to implement various tasks by the development language (c#, sql, vb6):

enter image description here

In the cell E8, I'd like to calculate the total amount of hours for C# work. E9 for SQL and E10 for VB6. Obviously, I can't use the SUM function. Is it possible to do what I want without eyeballing the spreadsheet and without using macros?

share|improve this question

marked as duplicate by Sathya Jun 3 '12 at 8:58

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

    
Similar question: superuser.com/questions/345064/… –  Ellesa Jun 3 '12 at 7:15

3 Answers 3

The equal sign is an operator useable in formulas. So something like

=(A1=B1)

will return TRUE if A1 is equal to B1, and FALSE if not.

You can convert it to a number with int

=int(A1=B1)

will return 1 if A1 is equal to B1, and 0 if not.

Through use of multiplication, for example, you can "zero out" values that don't fufill a condition, i.e.

=SUM( (C1*(int(A1=B1))), C2 )

This will give C1+C2, but only if A1=B1, otherwise it will just be the value of C2.

I guess you could test for specific strings in cells too (confession: I don't use Excel that much)

You can get very creative with it and write very long forumlas that will make your head hurt, look like you wrote it in Perl, and not be understandable when you revisit it in 6 months.

share|improve this answer

While I was unaware of your solution, another way to achieve this is to create a pivot table. If you have to create this kind of reports for your clients regularily, it may be a good idea to use one. This will allow you to group and analyse your data pretty much in many ways.

It may seem like a bit of overkill but actually,once you've done it for one data structure, it's straightforward.

Here's a link: http://chandoo.org/wp/2009/08/19/excel-pivot-tables-tutorial/

share|improve this answer
up vote 4 down vote accepted

Figured it out.

=SUMIF(D18:D26,"C#",E18:E26)
share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.