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.

I am given:

An excel sheet contains around 200 tasks (described in rows 2-201 in Column A). Each task can be elegible for a max of two projects (There are 4 projects in total, called "P1-P4" - drop down lists in Columns B and D); and this with a specific %-rate allocation (columns C & E - Column C refers to the Project Column B, and Column E refers to the Project in Column D). Column F shows the amount of work days spent on each task.

Example in row 2: Task 1 (Column A); P1 (Column B) ; 80% (Column C) ; P3 (Column D) ; 20% (Column E) ; 3 (Column F)

I need to know the sum of the working days spent on Project P3 respecting the %-rate for elegibility. I know how to calculate it for each Task (each Row) - e.g. for Task 1: =IF(B2="P3";C2*F2)+IF(D2="P3";E2*F2)

However instead of repeating this for each task, I need a formula that adds them all together. Unfortunately the following formula shows me an error: =IF(B2:B201="P3";C2:C201*F2:F201)+IF(D2:D201="P3";E2:E201*F2:F201)

Can anyone help please? Thank you!!

share|improve this question
    
It sounds like you want to use SUMIF. Something like SUMIF(B:B,"P3",C:C) would add the columns in column C where the corresponding row in Column B = "P3" –  Daniel Cook Oct 2 '12 at 15:21

1 Answer 1

You can use SUMPRODUCT() in this case to do what you want...multiply the % by the hours for matching projects. The following is one formula:

=SUMPRODUCT(--(B2:B201="P3"),C2:C201,F2:F201) 
        + SUMPRODUCT(--(D2:D201="P3"),E2:E201,F2:F201)

Essentially you are checking all of B against "P1" and if it equals, it is a 1, if it fails it is a 0, then you multiply that by the % in C and the Days in F.

Here's a great reference for the SUMPRODUCT formula and the tricks you can do with it: http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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