Help with Subtotal, CountIf, and Sum Product

[lizdraya]

Hello all,
I've been a long time lurker here, and you guys have been such a great help. However, I'm biting the bullet and made an account for the first time because I am completely lost. I consider myself a beginner in excel, I would not want to learn any Pivot tables or macros for this task. Here is the information:

I am using Excel 2010
[table="width: 500, class: grid, align: center"]
[tr]
[td]Column A[/td]
[td]Column B[/td]
[td]Column C[/td]
[td]Column D[/td]
[td]Column E [/td]
[td] Column F [/td]
[td] Column G [/td]
[td] Column H [/td]
[/tr]
[tr]
[td]Location[/td]
[td]Week Ending[/td]
[td]Employee Name[/td]
[td]Regular Hours Paid[/td]
[td]Pay Rate[/td]
[td] Overtime Hours Paid [/td]
[td] Overtime Rate[/td]
[td] Total Hours Paid [/td]
[/tr]
[tr]
[td]New York[/td]
[td]10/31/2015[/td]
[td]Joe [/td]
[td]20[/td]
[td]$10.00[/td]
[td] 5[/td]
[td] $15 [/td]
[/tr]
[tr]
[td]Texas[/td]
[td]10/31/2015[/td]
[td]Joe[/td]
[td]10[/td]
[td]$8.00[/td]
[/tr]
[tr]
[td]Florida[/td]
[td]10/31/2015[/td]
[td]Joe[/td]
[td]15[/td]
[td]$10[/td]
[/tr]
[tr]
[td] [/td]
[/table]

Ideally, I would like Excel to sum Regular Hours Paid + Overtime Hours Paid for each employee corresponding to their week ending for all the locations they have worked in .....row 6, Column H

I know, I can use the Subtotal Formula on the top, but I will add weeks to this and other people, so a formula would be ideal (one that does not remove everything]

Currently I am using
=IF(COUNTIFS(B:B,B2,C:C,C2),D2:F2,FALSE))

But, I would like a formula to subtotal it for me , so I have 1 row for Total Hours Paid

Please help,

 

[jtakw]

Yes, thank you so much for your explanation.

I noticed that excel slowed down like Windows 95 with the OFFSET calculations and I tried your new version.

You're welcome, so I guess it's working properly.