Emp ID wise Sum of Hours/amount and count of strength as per segregated categories!

[mmr1]

Hi experts,


Could you please suggest the formulas for to get solved the asked queries.

Thanks in advance,

 

[mmr1]

Hours Sum.xlsx
	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	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG
1																																																																			Sum of Total Hrs						Sum of Total Amount						Count of Total Strength
2	Emp Id	01-07-2020	01-07-2020	02-07-2020	02-07-2020	03-07-2020	03-07-2020	04-07-2020	04-07-2020	05-07-2020	05-07-2020	06-07-2020	06-07-2020	07-07-2020	07-07-2020	08-07-2020	08-07-2020	09-07-2020	09-07-2020	10-07-2020	10-07-2020	11-07-2020	11-07-2020	12-07-2020	12-07-2020	13-07-2020	13-07-2020	14-07-2020	14-07-2020	15-07-2020	15-07-2020	16-07-2020	16-07-2020	17-07-2020	17-07-2020	18-07-2020	18-07-2020	19-07-2020	19-07-2020	20-07-2020	20-07-2020	21-07-2020	21-07-2020	22-07-2020	22-07-2020	23-07-2020	23-07-2020	24-07-2020	24-07-2020	25-07-2020	25-07-2020	26-07-2020	26-07-2020	27-07-2020	27-07-2020	28-07-2020	28-07-2020	29-07-2020	29-07-2020	30-07-2020	30-07-2020	31-07-2020	31-07-2020	SumHrs	SumAmt		up to 10 hrs	10.1 to 14 hrs	14.1 to 24 hrs	Weekend Hrs	Holiday Hrs	Sum of Hrs after Weekend/Holidays Reduction	Normal Rate	10.1 to 14 Hrs OT1 Rate	14.1 to 24 Hrs OT2 Rate	Weekend Hrs Rate	Holidays Rate	Total Amount	up to 10 hrs	11 to 14 hrs	15 to 24 hrs	Weekend	Holidays	Count after Weekend/Holidays Reduction	Notes
3	Emp Id	Hrs	Amt	Hrs	Amt	Hrs	Amt	Hrs	Amt	Hrs	Amt	Hrs	Amt	Hrs	Amt	Hrs	Amt	Hrs	Amt	Hrs	Amt	Hrs	Amt	Hrs	Amt	Hrs	Amt	Hrs	Amt	Hrs	Amt	Hrs	Amt	Hrs	Amt	Hrs	Amt	Hrs	Amt	Hrs	Amt	Hrs	Amt	Hrs	Amt	Hrs	Amt	Hrs	Amt	Hrs	Amt	Hrs	Amt	Hrs	Amt	Hrs	Amt	Hrs	Amt	Hrs	Amt	Hrs	Amt	SumHrs	SumAmt									1.25	1.5	1.5	1.5								Normal rate to be consider for up to 10 Hrs
4	A1																			8	77.44																																											8	77.44								10												10.1 to 14 Hrs to be consider as a OT1,up to 10 Hrs is normal rate above 10 and below 14 hrs OT1 rate applicable
5	A2	10	95.8	10	95.8			10	95.8	10	95.8	10	95.8	10	95.8	10	95.8	10	95.8			8	76.64																																									88	843.04								10												14.1 to 24 Hrs to be consider as a OT2,up to 10 Hrs is normal rate above 10 and below 14 hrs OT1 rate applicable and above 14 to 24 hrs OT2 rate applicable.
6	A3	10	95.8	10	95.8	6	57.48	10	95.8	10	95.8	10	95.8	10	95.8	10	95.8	10	95.8			8	76.64																																									94	900.52								10												All Hours related to weekends/Hoildays to be considered as a OT2,Normal rate,OT1 rate not applicable.
7	A4	10	103.7	10	103.7	6	62.22	10	103.7	10	103.7	10	103.9	10	103.9	10	103.9	10	103.9	10	103.9	10	103.9	10	103.9	10	103.9	10	103.9	10	103.9	10	103.9	6	62.34	10	103.9	10	103.9	10	103.9	10	103.9	10	103.9	10	103.9			10	103.9	10	103.9	10	103.9	10	103.9	10	103.9	10	103.9			282	2,929.06								10
8	A5	10	95.8	10	95.8			10	95.8	10	95.8	10	95.8	10	95.8	10	95.8	10	95.8			8	76.64																																									88	843.04								10
9	A6	10	106.9	10	106.9			10	106.9	10	106.9	10	107	10	107	10	107	10	107			10	107	10	107	10	107	10	107	10	107					10	107	10	107	10	107	10	107	10	107	10	107			10	107	10	107	10	107	10	107	10	107					240	2,567.60
10	A7																																			10	112.4	10	112.4	10	112.4	10	112.4	10	112.4	10	112.4			10	112.4	10	112.4	10	112.4	10	112.4	10	112.4					110	1,236.40
11	A8	10	112.2	10	112.2	6	67.32			10	112.2	10	112.4	10	112.4	10	112.4	10	112.4			10	112.4	10	112.4	10	112.4	10	112.4			10	112.4																															126	1,415.52
12	A9																											10	106			10	106					10	106	11	116.6																							41	434.60
13																																																																0	-		-	-	-	-	-	-		1	2	2	2	-	-	-	-	-	-	-
Hours

Cell Formulas
Range	Formula
BL4:BL13	BL4	=+SUMIF($B$3:$BK$3,B$3,B4:BK4)
BM4:BM13	BM4	=+SUMIF($B$3:$BK$3,C$3,B4:BK4)
BU4:BU8	BU4	=+BM4/BL4
BO13:BT13,BV13:CF13	BO13	=SUM(BO3:BO12)

 

[alansidman]

crossposted: Tally number wise required sum of hours,amounts,count of strength as per categories.

 

[Fluff]

@mmr1
While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[mmr1]

Hi,

Thanks, noted

in future not to be repeated like cross posting in multiple forums now its unfortunately happens.