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]

Hi,

Is this what you need?

Excel 2010
	A	B	C	D	E	F	G	H
1	Location	Week Ending	Employee Name	Regular Hours Paid	Pay Rate	Overtime Hours Paid	Overtime Rate	Total Hours Paid
2	New York	10/31/2015	Joe	20	$10.00	5	$15
3	Texas	10/31/2015	Joe	10	$8.00
4	Florida	10/31/2015	Joe	15	$10
5
6								50
Sheet1

Cell Formulas
Range	Formula
H6	=SUM(SUMIFS(D2:D4,B2:B4,B2,C2:C4,C2),SUMIFS(F2:F4,B2:B4,B2,C2:C4,C2))

 

[lizdraya]

Okay, we're almost getting there. Thank you so much for your reply! You are a life saver

I have a data of 6k rows and 98 people, in other words a large amount of data.

I would really like to put the formula in H2 and scroll it down and have it do the math on (show 50) on H4, because that's where this example ends.

On row 5, it could still be the same employee, but for different weeks, or different employee for different weeks.

I will check to see if it works

 

[lizdraya]

I checked the Formula

So, I would really like to input the Formula on H2 and be able to scroll it down to the 6k row and have H2 through H3 blank, but on H4 it sums it up because that's when the criteria ends.

Am I making sense?

 

[jtakw]

Hi,

Alright, I think this will work for you:

Excel 2010
	A	B	C	D	E	F	G	H
1	Location	Week Ending	Employee Name	Regular Hours Paid	Pay Rate	Overtime Hours Paid	Overtime Rate	Total Hours Paid
2	New York	10/31/2015	Joe	20	$10.00	5	$15
3	Texas	10/31/2015	Joe	10	$8.00
4	Florida	10/31/2015	Joe	15	$10			50
Sheet1

Cell Formulas
Range	Formula
H2	=IF(OR(B2<>OFFSET(B2,1,0),C2<>OFFSET(C2,1,0)),SUM(SUMIFS(D$2:D$6000,B$2:B$6000,B2,C$2:C$6000,C2),SUMIFS(F$2:F$6000,B$2:B$6000,B2,C$2:C$6000,C2)),"")

Paste formula in H2, copy down column H, change the cell reference of 6000 to whatever you need if necessary.

Took me a little time, but that's because my brain wasn't working right, getting hungry, must make lunch.

 

[lizdraya]

Jtakw,
I understood the logic behind the 1st SUMIF function you gave me, correct me if I am wrong:

=SUM(SUMIFS(D2:D4,B2:B4,B2,C2:C4,C2),SUMIFS(F2:F4,B2:B4,B2,C2:C4,C2))

Sum the regular hours [D2:D4], within the week ending range column[B2:B4], for instance week ending [B2] and also the employee name range [C2:C4], for instances, [C2],
But Also
Sum OT HRS [F2:F4] within the week ending range column [B2:B4], for instances week ending [B2], but also corresponding to the employee name [C2:C4], for instances [C2]

Now for the life of me, I don't understand the theory behind your last formula:

=IF(B2<>OFFSET(B2,1,0),SUM(SUMIFS(D$2:D$6000,B$2:B$6000,B2,C$2:C$6000,C2),SUMIFS(F$2:F$6000,B$2:B$6000,B2,C$2:C$6000,C2)),"")

I made small revisions to it, for example:

=IF(B2<>OFFSET(B2,1,0),SUM(SUMIFS(D:D,B:B,B2,C:C,C2),SUMIFS(F:F,B:B,B2,C:C,C2)),0) and it has worked perfectly. I didn't see the need to have absolute values (this will be a continuous sheet) -- If this is a newbie problem, please let me know! . However, I don't understand the first IF

Let me take a shot
IF weekending [B2] does not equal to <>

OFFSET(Week Ending [B2], 1, 0),
And I'm lost....
What is Offset and what is the relationship to the formula?
Why do we need an OR with the same OFFSET formula?

I'm very grateful, but I see that I will be using these formulas in the future and I would not want to pester you with the same type of formula, but rather understand the formula.

Thank you so much for your time!

 

[jtakw]

Hi,

OK, back from lunch...

First of all, you're using the formula before I edited my post #5, please use the updated version...

=IF(OR(B2<>OFFSET(B2,1,0),C2<>OFFSET(C2,1,0)),SUM(SUMIFS(D$2:D$6000,B$2:B$6000,B2,C$2:C$6000,C2),SUMIFS(F$2:F$6000,B$2:B$6000,B2,C$2:C$6000,C2)),"")

I'll try to explain.

I have a data of 6k rows and 98 people, in other words a large amount of data.

I would really like to put the formula in H2 and scroll it down and have it do the math on (show 50) on H4, because that's where this example ends.

On row 5, it could still be the same employee, but for different weeks, or different employee for different weeks.

Because of your requirement above, the first IF is there to tell the SUM & SUMIFS when to perform the calculations, and if it's not the right time/place, then leave the cell "blank" in Column H.

The 2 conditions for knowing when is the right time/place to perform the calculations are the two OFFSET parts.
1st OFFSET, if B2 is not equal to the cell below it, meaning B3, because if B2 is the same as B3, we're still in the same Week Ending.
2nd OFFSET, if C2 is not equal to the cell below it, meaning C3, because if C2 is the same as C3, we're still looking at the same Employee.
So, if either one of these conditions are true, different Week Ending or different Employee, then perform the SUM & SUMIFS calculation, otherwise, leave cell blank.

Only one of these two tests need to be true for the calculations to proceed, because...
If the Week Ending is the same, but the Employee is different, then it's time to calculate.
If the Week Ending is different, but the Employee is the same, then it's time to calculate.
If the Week Ending is different, and the Employee is different, then it's time to calculate.
If the Week Ending is the same, and the Employee is the same, then it's not time yet.

Now, I see the changes you made regarding the absolute references, because you stated you have 6k rows of data, and since I have no idea what's beyond the 6k rows of data requiring this formula (maybe some other data?), I used absolute references just to be on the save side. One thing though, since you changed it to reference the entire Column, (D:D, B:B, F:F), the formula is referencing over a million rows.

And last, I actually realized I might have been a little over zealous in creating the formula, the following version will do the same:

=IF(OR(B2<>B3,C2<>C3),SUM(SUMIFS(D$2:D$6000,B$2:B$6000,B2,C$2:C$6000,C2),SUMIFS(F$2:F$6000,B$2:B$6000,B2,C$2:C$6000,C2)),"")

But at least now, hopefully, you learned another thing, the OFFSET function.

 

[lizdraya]

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.

 

[bosco_yip]

A combine of 2 Sumifs formula way :

=IF(OR(B2<>B3,C2<>C3),SUM(SUMIFS(INDIRECT({"D$2:D$1000","F$2:F$1000"}),B$2:B$1000,B2,C$2:C$1000,C2)),"")

 

[lizdraya]

Thanks, I'm afraid that INDIRECT is a volatile formula? And I'm using this type of formula in more than 1 columns