# Thread: If count formula counting hours threshold Thanks: 0 Likes: 0

1. ## If count formula counting hours threshold

Hi... I'd like to ask if someone can help me to find the correct formula.
I have an overtime tracking template for 31 days, the threshold total hours for the month is 303 for 30 days which is 10.1 per day, if an employee worked from 1-31, the threshold should retain 303, but if an employee worked let say from 3rd day til 31st day (29 days) the threshold should be 292.9, if 2nd to 31st the threshold will be 303. Thank you... thank you

=IF(COUNT(M10,P10,S10,V10,Y10,AB10,AE10,AH10,AK10,AN10,AQ10,AT10,AW10,AZ10,BC10,BF10,BI10,BL10,BO10,BR10,BU10,BX10,CA10, CD10,CG10,CJ10,CM10,CP10,CS10,CV10,CY10)*(303/31/24=0,"",COUNT(M10,P10,S10,V10,Y10,AB10,AE10,AH10,AK10,AN10,AQ10,AT10,AW10,AZ10,BC10,BF10,BI10,BL10,BO10,BR10,BU10,BX10,CA10,CD1 0,CG10,CJ10,CM10,CP10,CS10,CV10,CY10)*(303/31)/24)

2. ## Re: If count formula counting hours threshold

Hi,

Assuming you've got some columns scheduled for the days of the month starting at column M and the cell also contains the daynumber (ic from 1 till 31)
check this and see if it helps:
1Threshold12345678
2292,9xxxxxxx

Sheet1

Worksheet Formulas
CellFormula
L2=SUMPRODUCT(--(COLUMN(\$M2:\$CY2)=((\$M\$1:\$CY\$1)*3+10))*--(\$M2:\$CY2<>""))*(303/30)

3. ## Re: If count formula counting hours threshold

Hi Joris,

Thank you, I tried but it doesnt work on my spreadsheet...

 I J K L M N O P Q R S T U V W X DAY 1 DAY 2 DAY 3 DAY 4 NAME POS CODE 20 DAY TOTAL HOURS WRK POTENTIAL OT - HRS Total Hrs OT OT\$ Total Hrs OT OT\$ Total Hrs OT OT\$ Total Hrs OT OT\$ ROW THRESHOLD 10 ABARENTOS, ROLDAN W 202:00:00 206:34:00 4:34 10:18 0:12 \$1.10 10:24 0:18 \$1.64 10:35 0:29 \$2.65 10:33 0:27 \$2.47 20 ABDILAH, RONA BARI 40:24:00 36:46:00 0:00 9:12 0:00 \$ - 9:32 0:00 \$ - 10:02 0:00 \$ - 8:00 0:00 \$ -

Let say from Day 1 to Day 20... for 20 days the threshold is 202 which is showing for Roldan, but because Rona worked only until Day 5 her threshold is 40:24 which is also correct. But if another employee started to work from Day 5 until Day 31 (26 days) he/she should have 262.6 threshold which is 10.1*26 days but Roldan if worked 31 days his threshold will remain 10.1*30 which is 303, and not 10.1*31 (313.1)

On J10 the current formula to get the threshold is this=IF(COUNT(M10,P10,S10,V10,Y10,AB10,AE10,AH10,AK10,AN10,AQ10,AT10,AW10,AZ10,BC10,BF10,BI10,BL10,BO10,BR10,BU10,BX10,CA10, CD10,CG10,CJ10,CM10,CP10,CS10,CV10,CY10)*(303/31/24=0,"",COUNT(M10,P10,S10,V10,Y10,AB10,AE10,AH10,AK10,AN10,AQ10,AT10,AW10,AZ10,BC10,BF10,BI10,BL10,BO10,BR10,BU10,BX10,CA10,CD1 0,CG10,CJ10,CM10,CP10,CS10,CV10,CY10)*(303/31)/24)

However, with this formula by the time I fill up the Day 31... the threshold becomes 313.1.
If I change (303/31)/24) to 303/30/24 then Rona will have 252.5 threshold instead of 262.6

4. ## Re: If count formula counting hours threshold

Hi,

Change the formula to :

1Threshold1234567
2303xxxxxxx

Sheet1

Worksheet Formulas
CellFormula
L2=MIN(SUMPRODUCT(--(COLUMN(\$M2:\$CY2)=((\$M\$1:\$CY\$1)*3+10))*--(\$M2:\$CY2<>"")),30)*(303/30)