Count days in a column

rfletcher35

Active Member
Joined
Jul 20, 2011
Messages
300
Office Version
  1. 365
Platform
  1. Windows
Hi Guys,

I have a column that provides me with a day eg Mon, Tue etc from a particular date using the formula -

=IF(B2="","",DAY(AB2))

The problem is when I try to count how many "Mon" are in that column, I need to do this for all days of the week.

Day
Sun​
Mon​
Mon​
Tue​
Tue​
Wed​
Wed​
Wed​
Wed​
Wed​
Wed​

This is a section of my list as I have the cell formatted to 'DDD'

So really I need something like countif column AQ="Mon" for example but clearly that doesn't work

Can some help me please?

Thanks

Fletch
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
minisheet please
Client - Data.xlsm
ABACADAEAFAGAHAIAJAKALAMANAOAPAQAR
1Create Date/TimeCreated By ResourceCreated By ContactComplete Date/Time[required] Due Date/TimeFirst Response Date/TimeFirst Response Initiating ResourceResolutionCompleted ByCreate TimeFirst Response TimeDifferenceResultWeek NumDay
201/01/2021 04:53API, DattoRMM01/01/2021 04:5802/01/2021 04:0001/01/2021 04:58API, DattoRMMAPI, DattoRMM4:53 AM4:58 AM12:05 AMExcellent1Sun0
302/01/2021 18:55Vargas, Robert07/01/2021 14:5207/01/2021 23:0004/01/2021 09:14Hunt, StephenMehari, Merhawi6:55 PM9:14 AM########Excellent1Mon2
402/01/2021 18:56Vargas, Robert07/01/2021 13:4707/01/2021 23:0004/01/2021 09:15Hunt, StephenMehari, Merhawi6:56 PM9:15 AM########Excellent1Mon2
Original Data
Cell Formulas
RangeFormula
AL2:AL4AL2=TIME(HOUR(AB2),MINUTE(AB2), SECOND(AB2))
AM2:AM4AM2=TIME(HOUR(AG2),MINUTE(AG2), SECOND(AG2))
AN2:AN4AN2=AM2-AL2
AO2:AO4AO2=IF(AN2<'Stats Data'!$D$31,"Excellent",IF(AN2<'Stats Data'!$D$30,"Good",IF(AN2<'Stats Data'!$D$29,"Acceptable","Poor")))
AP2:AP4AP2=IF(B2="","",WEEKNUM(AB2,2))
AQ2:AQ4AQ2=IF(B2="","",DAY(AB2))
AR2AR2=COUNTIF(AQ:AQ,0)
AR3:AR4AR3=IF(B3="","",DAY(AB3))


Hope I did this correct?
 
Upvote 0
Client - Data.xlsm
ABACADAEAFAGAHAIAJAKALAMANAOAPAQAR
1Create Date/TimeCreated By ResourceCreated By ContactComplete Date/Time[required] Due Date/TimeFirst Response Date/TimeFirst Response Initiating ResourceResolutionCompleted ByCreate TimeFirst Response TimeDifferenceResultWeek NumDay
201/01/2021 04:53API, DattoRMM01/01/2021 04:5802/01/2021 04:0001/01/2021 04:58API, DattoRMMAPI, DattoRMM4:53 AM4:58 AM12:05 AMExcellent1Sun0
302/01/2021 18:55Vargas, Robert07/01/2021 14:5207/01/2021 23:0004/01/2021 09:14Hunt, StephenMehari, Merhawi6:55 PM9:14 AM########Excellent1Mon2
402/01/2021 18:56Vargas, Robert07/01/2021 13:4707/01/2021 23:0004/01/2021 09:15Hunt, StephenMehari, Merhawi6:56 PM9:15 AM########Excellent1Mon2
Original Data
Cell Formulas
RangeFormula
AL2:AL4AL2=TIME(HOUR(AB2),MINUTE(AB2), SECOND(AB2))
AM2:AM4AM2=TIME(HOUR(AG2),MINUTE(AG2), SECOND(AG2))
AN2:AN4AN2=AM2-AL2
AO2:AO4AO2=IF(AN2<'Stats Data'!$D$31,"Excellent",IF(AN2<'Stats Data'!$D$30,"Good",IF(AN2<'Stats Data'!$D$29,"Acceptable","Poor")))
AP2:AP4AP2=IF(B2="","",WEEKNUM(AB2,2))
AQ2:AQ4AQ2=IF(B2="","",DAY(AB2))
AR2AR2=COUNTIF(AQ:AQ,0)
AR3:AR4AR3=IF(B3="","",DAY(AB3))


Hope I did this correct?
You will notice in Column AR that I have tried something
 
Upvote 0
Hi RFletcher35,

I cannot believe that the AQ formula is really what you want. It is putting the day of the month from the AB column. You are then formatting it as ddd but because it's a single number it assumes that must be from the base date of 1st January 1900.
e.g. Your first date is 1/1/2021 which was a Friday. Cell AQ2 therefore contains just the day, 1, but 1st January 1900 was a Sunday, so AQ2 is reformatted to "Sun".

I believe it may be easiest to change the AQ formula to
Excel Formula:
=IF(B2="","",TEXT(AB2,"ddd"))
then you can just use
Excel Formula:
=COUNTIF(AQ:AQ,"Mon")
to count the Mondays.
 
Upvote 0
Solution
Hi RFletcher35,

I cannot believe that the AQ formula is really what you want. It is putting the day of the month from the AB column. You are then formatting it as ddd but because it's a single number it assumes that must be from the base date of 1st January 1900.
e.g. Your first date is 1/1/2021 which was a Friday. Cell AQ2 therefore contains just the day, 1, but 1st January 1900 was a Sunday, so AQ2 is reformatted to "Sun".

I believe it may be easiest to change the AQ formula to
Excel Formula:
=IF(B2="","",TEXT(AB2,"ddd"))
then you can just use
Excel Formula:
=COUNTIF(AQ:AQ,"Mon")
to count the Mondays.
Perfect, thank you
 
Upvote 0
due to local setting i did it on Polish weekday names, you can change it to ENg column when u open the file
Book4
ABCD
1Create Date/TimeCreated By ResourceCreated By ContactComplete Date/Time
201-01-2021 04:53API, DattoRMM01-01-2021 04:58
302-01-2021 18:55Vargas, Robert07-01-2021 14:52
402-01-2021 18:56Vargas, Robert07-01-2021 13:47
5
6
7
8
9Polish:English:count
10NiedzSun1
11PonMon2
12PonMon2
13
Sheet1
Cell Formulas
RangeFormula
A10:A12A10=PROPER(TEXT(DAY(A2:A4),"DDD"))
C10:C12C10=COUNTIFS(A10#,A10#)
Dynamic array formulas.
 
Upvote 0
due to local setting i did it on Polish weekday names, you can change it to ENg column when u open the file
Book4
ABCD
1Create Date/TimeCreated By ResourceCreated By ContactComplete Date/Time
201-01-2021 04:53API, DattoRMM01-01-2021 04:58
302-01-2021 18:55Vargas, Robert07-01-2021 14:52
402-01-2021 18:56Vargas, Robert07-01-2021 13:47
5
6
7
8
9Polish:English:count
10NiedzSun1
11PonMon2
12PonMon2
13
Sheet1
Cell Formulas
RangeFormula
A10:A12A10=PROPER(TEXT(DAY(A2:A4),"DDD"))
C10:C12C10=COUNTIFS(A10#,A10#)
Dynamic array formulas.
Thanks for your assistance, I already used Toadstolls solution when you sent this, but thanks anyway. Fletch
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

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
Back
Top