Wanting to count dates within a column

ShannonT

Hello,

I'm trying to create a monthly report from quarterly data. I want a formula that will count how many projects went live in a given month from a column of dates. In the screen shot, I want to put a formula in the blue cell that tallies the Actual Live Date column.

Thoughts? Thanks!

Shannon

ker9

=Count()

Enter the range in the parenthesis by clicking and dragging.  It will count non blank cells.

=COUNT(Actual Live Date1:Actual Live Date999)

ShannonT

Thank you, ker9, for your response. But that formula will count all of the dates in the column range. I want to count only October dates.

ker9

If you can add a helper column, you can get by month:

Add a helper Month column that looks at the Actual Live Date and returns the month number:

=IF(ISBLANK(Actual Live Date62), 0, MONTH(Actual Live Date62))

Then count if

=COUNTIF([Month Number]2:[Month Number]63, 11)

(where 11 is the month you want to count)

You can also use CountIfs to return multiple criteria (multiple months)

You might be able to get this all in one formula but I didn't try it.

J. Craig Williams

First, this

=COUNT(Actual Live Date1:Actual Live Date999)

does not get the whole column. It will generate an #UNPARSEABLE error because the column names have spaces in them and require square brackets []. If that were fixed, it would only get the first 999 rows.

This will get the whole column

=COUNT([Actual Live Date]:[Actual Live Date])

In the blue cell, this formula will get the Actual Live Dates that are October.

=COUNTIF([Actual Live Date]:[Actual Live Date], IFERROR(MONTH(@cell), 0) = 10)

That may not quite be what you want, however, so this will get October 2017

=COUNTIFS([Actual Live Date]:[Actual Live Date], IFERROR(MONTH(@cell), 0) = 10, [Actual Live Date]:[Actual Live Date], IFERROR(YEAR(@cell), 0) = 2017)

If that work for you, I recommend you have somewhere that you enter 10 and 2017 so you don't need to modify the formula.

Even better would be a cells for "Month of Concern" and "Year of Concern". If that was just a date cell that isn't being used by other formulas (or accounted for by them), then that would be even easier. Let's say you put it into row 7 of the Actual Live Date (the same row as the blue cell)

=COUNTIFS([Actual Live Date]:[Actual Live Date], IFERROR(MONTH(@cell), 0) = MONTH([Actual Live Date]7), [Actual Live Date]:[Actual Live Date], IFERROR(YEAR(@cell), 0) = YEAR([Actual Live Date]7)) - 1

Note that I did not wrap the new part (in bold) with IFERROR because I expect there to be a date there. The -1 at the end accounts for this new entry. 

The last caveat would be it is better if that month/year of concern was somewhere else ... because someone else may not realize they need to account for it in their formulas.

I hope this helps.

Craig

 

Team Orbie 9

Hi there Craig,

This all makes sense but how would I write the formula to count for multiple months, like Jan (01) to May (05)? I need to report YTD as well as monthly as we move through the year.

J. Craig Williams

There are so many ways to go on what you are asking for, that it is impossible to give a definitive answer.

I can build rolling years, rolling quarters, current month, previous week, whatever is needed by the user. But it all depends on what, exactly, is needed.

If you are looking for YTD, then something with YEAR(date) = YEAR(TODAY()) should be sufficient.

If you are looking for current month, then something with AND(MONTH(date)=MONTH(TODAY()),YEAR(date)=YEAR(TODAY())) should be in the tool box.

Last month? More complicated because late month may be December.

I just finished creating a rolling 5 quarter system for a customer, with dashboard that will automatically change from "Current Quarter April - June 2018" to "Current Quarter July - September 2018" on July 1st. All the other quarters (previous, next three) will update too.

But that took me a couple days and isn't a single formula that would be easy to share.

One bit of advice, though, is that it is better to use "Current Year" than "2018" because any Reports or Dashboards don't need to be updated for the current values, but would it they were pointing to a sheet that had 2018 in the name.

I hope this helps. 

Craig

jonathan.hunter253751

Hi Craig, 

 

What is the @cell reference for? What information needs to go in that space.

I am doing a similar function for adding up years (2017)(2018)etc but the first formula you provided just comes back with zero for me.

I have a sheet that contains several years of information and want to count the amount of dates for 2017 and 2018 etc.

 

J. Craig Williams

@cell is a reference to the cell returned by the formula

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

Can you post your formula or screen shot same?

Craig

nbetters65246

I am needing to count projects that are starting on x "week" based on a 52 week schedule, for instance I would want my dashboard to show "Week 1 has 5 Project Starts" Week 2 has 15 Project Starts" "Week 52 has 12 Project Starts" etc. etc. I would then mimic the calculations for project finishes, project scheduled audits etc. 

 

I cannot seem to figure this out shy of doing count if and then having a column for every single week of the year which seems a little obsessive when an excel pivot table will do exactly this. 

Andrée Starå

Hi,

Try this.

Formulas

Weekstart: 

=IFERROR(WEEKNUMBER(Startdate@row); "Week from Startdate")

Number of projects per week: 

=COUNTIF(Weekstart:Weekstart; Weeknumber@row)

Dashboard Text:

=(" Week " + Weeknumber@row + " has " + [Number of projects per week]@row + " Project Starts ")

Please see the attached link/screenshot for more information.

Number of projects per week

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

You could also have a reference sheet for the weeks.

Would this work for you?

I hope this helps you!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Tribeiro

how about if you only want to count the months filled in the parent field?