Formula to count # of dates per month in a date column

kylevan01

I am trying to create a formula that will tally, by month, the number of date entries in a column.  (See screenshot attached).

Our non-profit provides ongoing financial counseling to veterans and each person is counseled more than once throughout the year...so I want to be able to tally each month how many new veterans we counsel by referencing the initial date column and then tallying above each month.  This way a veteran counseled 4 times throughout the year is only counted once (as new in month X), and we can easily see how many new veterans we counseled by month and then roll up to a total sheet.  In my screenshot example Jan # of Heroes would populate with 1, Feb 2, Mar 1 and so forth.  I want the formula for each month to tell me how many dates in the initial date column fall within the date range of each month. 

Please help!  I thought I had it by looking at some other items in this forum but my formula did not work. 

Mike Wilday

This formula will count all January dates in the Initial Date column. You can change the number at the end of the formula to account for whichever month you are looking for. 1=January, 2=February, 3=March, etc. 

=COUNTIFS([INITIAL DATE]:[INITIAL DATE], IFERROR(MONTH(@cell), 0) = 1)

This next one is enhanced to only count 2018 dates, although your sheet seems to be based on year anyhow. 

=COUNTIFS([INITIAL DATE]:[INITIAL DATE], IFERROR(MONTH(@cell), 0) = 1, [INITIAL DATE]:[INITIAL DATE], IFERROR(YEAR(@cell), 0) = 2018)

Mike Wilday

To clarify, I added an IFERROR, because if you have cells you are counting that do not have dates populated it would throw an error, If there is an empty date cell the countif formula will ignore those rows. 

Paul Newcome

I second Mike's suggestion. I especially like the IFERROR being used to look specifically at the month. I usually just wrap my entire formula in the IFERROR statement. I never thought to use it like this, but it opens up so much more flexibility.

 

Good thinking, Mike!

kylevan01

Absolutely worked!

 

Thanks guys I went with the =COUNTIFS([INITIAL DATE]4:[INITIAL DATE]20, IFERROR(MONTH(@cell), 0) = 1) formula.

We will begin adding more and more years for each counselor to show their yearly/historic breakdown individually and I will be linking the roll-up data to high level company wide sheet as well.

Thanks so much!  This will allow me to publish this to the counseling team next week.  Very impressive.

Mike Wilday

Sweet. Glad I could be of assistance.

Yeah, that IFerror in the middle of the formula doesn't interfere with the whole count, it will just add a 0 at the point of breakage, which would be on a single cell in the countif. Since it would be a 0 if there were no date, the IFERROR there simply adds the 0 at that point and keeps counting. Which is exactly what is needed when counting an entire column of dates and empty cells! Super useful. 

LaToya Tapscott

Hi everyone,

 

This is similar to what I am trying to do but for whatever reason I keep receiving an error. The only difference is that I am pulling my dates column from another sheet.

=COUNTIFS({Law Firm Alumni Program Range 2}), IFERROR(MONTH(@cell), 0) = 4)

I entered this formula to capture all date entries during the month of April. Am I doing something wrong?

 

Thank you!

J. Craig Williams

You have an extra ) before the first comma.

Craig

Neisha Fredericks

I think this is where I found the solution previously (which was working until today when I tried to copy the formula somewhere else). I am trying to count the number of rows for a specific month and year where the hierarchy is "0". I am getting an incorrect argument with the below function. What did I do wrong?

=COUNTIFS({OSPO Engagements Archive complete date}, IFERROR(MONTH(@cell), 0) = 1, {OSPO Engagements Archive Hierarchy}, =0, {OSPO Engagements Archive complete date}, IFERROR(YEAR(@cell), 0) = 2019)

 

 

Ozu

Hello All,

I am trying to do the same but It's adding the number and secondly giving 1 as the number to all.

The holiday calendar Range 1 is the date column as "start date"

Need to pull all date for the month of January

KPH

This formula is counting how many rows in your Start Date column contain a date that is in the month of January.

It is a be easier to understand without the IFERROR, when it looks like this

=COUNTIFS({Holiday Calendar Range 1}, (MONTH(@cell)) = 1)

=COUNTIFS tells the cell to count based on criteria

{Holiday Calendar Range 1} identifies the column to look in

(MONTH(@cell)) = 1 says to look dates with a month of 1

@Mike Wilday has added a clever IFERROR to count 0 if there is a cell without a month in the column (this stops the formula from breaking when blank)

=COUNTIFS({Holiday Calendar Range 1}, IFERROR(MONTH(@cell), 0) = 1)

I am not sure what you mean by It's adding the number but the reason it is giving 1 as the number to all is because there is nothing in that formula to specify a particular person. All rows will return the same result. If you want to count the number of rows with start dates in January for a particular person, you need to add a second range and criteria to the formula. Something like this

=COUNTIFS({Holiday Calendar Range 1}, IFERROR(MONTH(@cell), 0) = 1, {Holiday Calendar Range containing names}, [Column name in current sheet that contains now of person to count]@row)

Ozu

Thanks @KPH it answers my question and you explained it well. 😀