sumifs (or sumproduct) but with division - and a lookup

I need to calculate the total # of hours worked on an individual project and project type.

Column A contains employee name, column B contains project name, column C contains project type, and column D contains total cost. Elsewhere on this worksheet is a (lookup) table (F2:G4) that contains a unique list of employee names (column F) and their hourly rate (column G).

I want to calculate total hours worked on a specific project of a specific type (multiple criteria). My first approach was to use sumifs to calculate total cost, then divide this value by the employees hourly rate. I tried this to calculate the total cost: =SUMIFS(D2:D7, B2:B7, "A", C2:C7, "ZZ")

This worked great, but the next step is where I encountered a problem. To calculate the total hours I have to first divide the cost by the employee's hourly rate before summing them. Maybe I have to use a sumproduct division technique, but I couldn't get it to work. How can I calculate total hours worked by project (and project type)?

Originally Posted by daddylonglegs

Try using SUMPRODUCT for total hours with a SUMIF included which divides by the hourly rate, i.e.

=SUMPRODUCT((B2:B7= "A")*(C2:C7= "ZZ"),D2:D7/SUMIF(F2:F4,A2:A7,G2:G4))

Thanks for the response daddylonglegs! How can I update this formula to gracefully handle divide by zero cases? Thanks again.

CalcTotalHoursForProjectATypeZZ.png

Here's what the sheet looks like. The divide by zero error occurs because Gary is not in the lookup table. How do I update =SUMPRODUCT((B2:B7= "A")*(C2:C7= "ZZ"),D2:D7/SUMIF(F2:F4,A2:A7,G2:G4)) to handle divide by zero cases? Cheers

Brilliant! Thank you kindly daddylonglegs