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)