Re: How to count Sundays worked in a specific month based on a cell value

[KyleOliver]

Re: How to count Sundays worked in a specific month based on a cell value

Hi All,

I need to count the Sundays that an employee worked in a month.

The below table is what I am using.
According to this table the employee has worked one Sunday out of the two Sundays shown in the table.

The formula I used was =COUNTIF(H1:H17">=1") but this counts all the cells in column H where the hours exceed 1 and not the Sundays only.

I don't know what formula to use, please help?

	A	B	C	D	E	F	G	H
1	1	Week Day	Punch Date	Time In	Lunch Start	Lunch End	Time Out	Hours Worked
2	2	Wednesday	Wed 01-Aug-18	8:00	12:00	13:00	16:00	7:00
3	3	Thursday	Thu 02-Aug-18	8:00	12:00	13:00	16:00	7:00
4	4	Friday	Fri 03-Aug-18	8:00	12:00	13:00	16:00	7:00
5	5	Saturday	Sat 04-Aug-18	8:00	12:00	13:00	16:00	7:00
6	6	Sunday	Sun 05-Aug-18	8:00	12:00	13:00	16:00	7:00
7	7	Monday	Mon 06-Aug-18	8:00	12:00	13:00	16:00	7:00
8	8	Tuesday	Tue 07-Aug-18	8:00	12:00	13:00	16:00	7:00
9	9	Wednesday	Wed 08-Aug-18	8:00	12:00	13:00	16:00	7:00
10	10	Thursday	Thu 09-Aug-18	8:00	12:00	13:00	16:00	7:00
11	11	Friday	Fri 10-Aug-18	8:00	12:00	13:00	16:00	7:00
12	12	Saturday	Sat 11-Aug-18	8:00	12:00	13:00	16:00	7:00
13	13	Sunday	Sun 12-Aug-18
14	14	Monday	Mon 13-Aug-18	8:00	12:00	13:00	16:00	7:00
15	15	Tuesday	Tue 14-Aug-18
16	16	Wednesday	Wed 15-Aug-18	8:00	12:00	13:00	16:00	7:00
17

<thead style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 13px; margin-bottom: 0px;">
</thead><tbody style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 13px; margin-bottom: 0px;">
</tbody>

 

[Aladin Akyurek]

Re: How to count Sundays worked in a specific month based on a cell value

Looks like you want...

=COUNTIFS(B:B,"sunday",H:H,"<>")

 

[KyleOliver]

Re: How to count Sundays worked in a specific month based on a cell value

Looks like you want...

=COUNTIFS(B:B,"sunday",H:H,"<>")

Hi Aladin,

Thank-you for your response, I appreciate it.

=COUNTIFS(B:B,"sunday",H:H,"<>") ​

does not seem to work .
I tried

=COUNTIFS(B16:B46,N22,H16:H46,"> 0") but did not work, I then tried =COUNTIFS(B16:B46,N22,H16:H46,"> 0") but this also doesn't work.
Cell N22 holds the "Sun" text.
​

 

[Aladin Akyurek]

Re: How to count Sundays worked in a specific month based on a cell value

What do we have in B16:B46? Formula? Text? Date? If text, is it like Sun or Sunday?

 

[KyleOliver]

Re: How to count Sundays worked in a specific month based on a cell value

Hi Aladin, The B column is formated with "Custom" "ddd" and column H is formatted with "Number" 0.00

Hi Aladin, the B column is formatted with "Custom" "ddd" and column H is formatted with "Number" 0.00.

 

[Aladin Akyurek]

Re: How to count Sundays worked in a specific month based on a cell value

Hi Aladin, The B column is formated with "Custom" "ddd" and column H is formatted with "Number" 0.00

Hi Aladin, the B column is formatted with "Custom" "ddd" and column H is formatted with "Number" 0.00.

In that case try:

=SUMPRODUCT(--(TEXT(B2:B2000,"ddd")="Sun"),--ISNUMBER(1/H2:H2000))

 

[KyleOliver]

Re: How to count Sundays worked in a specific month based on a cell value

You are an absolute GENIUS!!!
Thank-you so much Aladin, I just copied-and-pasted your code and it works perfectly.
THANK-YOU, THANK-YOU, THANK-YOU......…​

Regards,
Kyle​

 

[Aladin Akyurek]

Re: How to count Sundays worked in a specific month based on a cell value

Kyle,

You are welcome.