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

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.

count certain dates in column and total.jpg

Find more posts tagged with

Non-profit

Formulas

Comments

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. 😀

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?