# Thread: COUNTIF Weekends (2003 SP3)

1. ## COUNTIF Weekends (2003 SP3)

Good morning

I have a column that identifies weekend days from another column, if overtime is done on weekends the drivers get paid 1.5 times for a Saturday and 2.0 times for a Sunday so I would like to know the amount of weekend overtime has been worked, I have now inserted 2 cells 'Overtime Hours Saturday' and 'Overtime Hours Sunday ' and thought that a COUNTIF function might do it, I have tried =COUNTIF(\$B\$1:\$B\$31)=WEEKDAY(A1,2)>5 thinking that would add up any numbers (note: Column B is populated by calculations from other cells and will contain either nothing or a number)

Assuming that this will work and I have just made a stupid mistake somewhere how do you identify Saturday and Sunday seperatley using the Weekday greater than 5 function

Cheers

Steve

2. ## Re: COUNTIF Weekends (2003 SP3)

Does this do what you want?

=SUMPRODUCT((WEEKDAY(A1:A31,2)>5)*B1:B31)

I have assumed that the dates are in A1:A31.

3. ## Re: COUNTIF Weekends (2003 SP3)

Thanks Hans

Thats a great start but I need to seperate the Saturday and Sunday hours, what can I change in this formula to identify those =SUMPRODUCT((WEEKDAY(A1:A31,2)>5)*B1:B31) - To identify the Sunday I realise I can change it to =SUMPRODUCT((WEEKDAY(A1:A31,2)> <font color=red>6</font color=red> )*B1:B31), but I can't seem to identify the Saturday,

Cheers

Steve

4. ## Re: COUNTIF Weekends (2003 SP3)

To sum the Saturday hours:

=SUMPRODUCT((WEEKDAY(A1:A31,2)=6)*B1:B31)

To sum the Sunday hours:

=SUMPRODUCT((WEEKDAY(A1:A31,2)=7)*B1:B31)

(1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday, 7 = Sunday)

5. ## Re: COUNTIF Weekends (2003 SP3)

Hi Steve

Try:

=SUMPRODUCT((WEEKDAY(A1:A31,2)= 6 )*B1:B31)

6. ## Re: COUNTIF Weekends (2003 SP3)

Thanks Hans and Jerry

I struggled with this all day, it is amazing what the eye does not see, because the earlier examples showed > than I assumed it had stayed the same and I had not noticed the subtle change to =, for example =SUMPRODUCT((WEEKDAY(A1:A31,2) <font color=red>=</font color=red> 6)*B1:B31) and I was still using the > sign and pulling my hair out.

Cheers as usual

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•