Sum based on filter and if statement.

[magges]

Hi All,

I have a bit of challenge in terms of creating an overview of employee hours spent on different projects. The "Total weekly" row 4

As seen on the image below i have employees on project 1 and 2, column A. their weekly hours can be seen from column F. On the image below the sum of the hours is 30 (F4).

However i would like to have the "Total weekly" sum being depended on filters. so if i filter to only see "project 1" as below, the sum should adapt to this.
In this case it should be 10 instead of 30.

I have tried using SUMPRODUCT combined with an IF statement, but i cannot get to work as intended. At the moment i am only able to get the sum of all cells.
I need the IF statement in order to only sum for the parameter in column E. So in F4 i only get the hours related to "Budget"

Is there any way to do this with formulas or do i need a macro for the job?
Link to dropbox with the Excel file from the screenshots

 

[JimM]

Have a look at the SUBTOTAL function to see if it will work for you eg =SUBTOTAL(109,F4:F50)

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=SUMPRODUCT(SUBTOTAL(109,OFFSET(F16,ROW(F16:F52)-ROW(F16),0,1))*($E$16:$E52=$E4))

 

[magges]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=SUMPRODUCT(SUBTOTAL(109,OFFSET(F16,ROW(F16:F52)-ROW(F16),0,1))*($E$16:$E52=$E4))

Thank you very much. Works perfect

 

[Fluff]

Glad we could help & thanks for the feedback.