Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 Oct 2 '12 at 15:21

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .