# How can I write an excel formula to do row based calculations; where certain conditions need to be met?

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!!

-
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)
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/