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

KyleOliver

New Member
Joined
Apr 16, 2016
Messages
31
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? :confused:


ABCDEFGH
11Week DayPunch DateTime InLunch StartLunch EndTime OutHours Worked
22WednesdayWed 01-Aug-188:0012:0013:0016:007:00
33ThursdayThu 02-Aug-188:0012:0013:0016:007:00
44FridayFri 03-Aug-188:0012:0013:0016:007:00
55SaturdaySat 04-Aug-188:0012:0013:0016:007:00
66SundaySun 05-Aug-188:0012:0013:0016:007:00
77MondayMon 06-Aug-188:0012:0013:0016:007:00
88TuesdayTue 07-Aug-188:0012:0013:0016:007:00
99WednesdayWed 08-Aug-188:0012:0013:0016:007:00
1010ThursdayThu 09-Aug-188:0012:0013:0016:007:00
1111FridayFri 10-Aug-188:0012:0013:0016:007:00
1212SaturdaySat 11-Aug-188:0012:0013:0016:007:00
1313SundaySun 12-Aug-18
1414MondayMon 13-Aug-188:0012:0013:0016:007:00
1515TuesdayTue 14-Aug-18
1616WednesdayWed 15-Aug-188:0012:0013:0016:007: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>
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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

New Member
Joined
Apr 16, 2016
Messages
31
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 :confused:.
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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

New Member
Joined
Apr 16, 2016
Messages
31

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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

New Member
Joined
Apr 16, 2016
Messages
31
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......…
(y)
Regards,
Kyle
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
Re: How to count Sundays worked in a specific month based on a cell value

Kyle,

You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,247
Messages
5,623,603
Members
415,981
Latest member
Baltwin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top