VBA - Sum issue

[gd6noob]

Hi there,

I have a spreadsheet that has a list of hours for each day an employee worked. Each day is listed in a vertical table.
This is the formula Im using for one of the column for the Input tab:

Range("K2:K" & LastRow4).formula = "=SUMPRODUCT(($A2='Input 2'!$C$2:$C$3000)*(""CAN ""&$K$1&"" Hours Worked""='Input 2'!$L$1:$AZ$1),'Input 2'!$L$2:$AZ$3000)/24"

While this work, it takes a long time to process and I have a lot of columns to run through. Sometimes I have too much data from Input2 tab, that excel crashes on me.
Also the reason Im using wildcards to search is that sometimes the data in Input2 is different. i.e "CAN Regular Hours Worked" can be labelled as ""CAN ON Regular Hours Worked" and so forth, depends on the region where the employees are working in. the number of columns can also differ based on region.

Is there a better method so sum up the total hours worked?

60 Hour Tool V3.0 (Make copy).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
1	Apply Date	Historical Date	Employee ID	Employee Name	Employee Logon	EE Type	Manager	Pay Rule	Location	Cost Center	Last Hire Date	CAN Approved Time Off	CAN Attendance Remove 0.5 Pts	CAN Attendance Remove 1.0 Pts	CAN Attendance Remove 1.5 Pts	CAN Banked Holiday	CAN Bereavement Paid	CAN Bereavement Unpaid	CAN Company Business	CAN Excused	CAN Meal Exception	CAN Night Shift Premium	CAN Night Shift Premium OT	CAN Non Worked Paid Time	CAN ON Holiday	CAN Overtime	CAN Paid Suspension	CAN Paid Time Off	CAN Regular Hours Worked	CAN Role Premium 1	CAN Role Premium OT1	CAN Show Up Pay	CAN Vacation Paid	CAN Voluntary Time Off	GBL FC Missed Time Tracking
2	3/10/2021		108444603	abc		H			YYZ4	1299	10/18/2020																		10.20
3	3/11/2021		108444603	abc		H			YYZ4	1299	10/18/2020																		10.10
4	3/7/2021		108197952	abc		H			YYZ4	1299	11/5/2020											10.15							10.15
5	3/8/2021		108197952	def		H			YYZ4	1299	11/5/2020											10.12							10.12
6	3/9/2021		108197952	def		H			YYZ4	1299	11/5/2020											10.17							10.17
7	3/10/2021		108197952	def		H			YYZ4	1299	11/5/2020											9.57	0.60			0.60			9.57
Input 2

60 Hour Tool V3.0 (Make copy).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Employee ID	Employee Name	Employee Logon	Payroll ID	Pay Group	Schedule Group	EE Type	Dept	Manager Name	Agency	Regular	Overtime	Doubletime	Holiday	Holiday Overtime	Holiday Doubletime	Banked Holiday	Paid Personal Time	Vacation	Total Regular Hours	Total OT Hours	Total Paid Time Off	Total Payable Hours	Total Unpaid Time Off
2	104981261							1189
3	108419407							1290
4	109394474					DB1C0730		1299040
5	100096392					NA0C1745		1299010
6	104678271					DB0C0645		1299020
Input

 

[Alex Blakenburg]

As far as I can tell, at the moment your formula is only totaling 1 column from Input 2.
If this can be relied on then SumIfs will work for you and is far more efficient.

I would also suggest you consider using tables. By using tables and Table structure formulas you won't need to go out to column AZ and Row 3000 as the formulas will adjust to the table size.

The below assumes the Input sheet to be the activesheet.

=SUMIFS(INDEX('Input 2'!$L$2:$AI$3000,0,MATCH("CAN "&K$1&" Hours Worked",'Input 2'!$L$1:$AZ$1,0)),
'Input 2'!$C$2:$C$3000,$A2)

 

[gd6noob]

Im thinking if this way is more feasible.

Search/Finding the cells I need in row 1 and move the entire column closer....

 

[Alex Blakenburg]

Im thinking if this way is more feasible.

Search/Finding the cells I need in row 1 and move the entire column closer....

Not really following what you mean.

 

[gd6noob]

Not really following what you mean.

Sorry, to clarify.

Use a code to find on sheet2 "Regular Hours Worked", then cut that entire column or until end of data and insert it to column B for example. Then repeat for all the columns I need, then when when I use the sum function, it wont be from A:AL but to A:D or something like that.

 

[Alex Blakenburg]

Sorry, to clarify.

Use a code to find on sheet2 "Regular Hours Worked", then cut that entire column or until end of data and insert it to column B for example. Then repeat for all the columns I need, then when when I use the sum function, it wont be from A:AL but to A:D or something like that.

Rereading your requirements and comments it still looks like you only want to sum each employee for 1 column, the column being selected based on a lookup value.
If that is the case did the sumifs not work for you ?
You can add a wildcard into the formula in place of CAN.
(I forgot to add the /24 you had in yours. If you want to report in days, you will need to add that in.)

If you want more than 1 column but only 2 or 3 then Sumifs1 + Sumifs2 + Sumifs3, would be worth considering.

If its more than that there are alternate design options we can go into.

 

[gd6noob]

Thats correct, summing total hours per column, but I thought that if moving the required columns closer to the Employee ID, the lookup range would be smaller and more efficient, or would that not be an issue. Since the number of rows are around 20,000 - 24,000.

 

[Alex Blakenburg]

The distance will matter for the SumProduct which creates a huge array that it cross multiplies. The Sumifs won't be impacted and should be more efficient even than SumProduct with the columns rearranged.
With larger volumes of data Power Query and Power Pivot are likely to scale up better.