How to use countifs with dates.
Hello.
I am trying to count the number of projects in each quarter for the past 3 years:
I tried to use a formula like this to count the number of projects in the first quarter in 2018:
=COUNTIF({Service Range 1}; < 142018)
how do I make the formula work? (or is it because the date format is wrong)
Best Answer

@Frederik Soerensen your date format in the formula will need to match the date column formatting on the source sheet. Your formula formatting is also incorrect as you are using ; instead of a , after the reference range.
Is the Opening Date on the Service sheet formatted as a date? It doesn't appear to be as it is not one of Smartsheets predefined formats
Answers

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

I am trying to make a graph to show how many projects there are in each quarter. I have made the underlying sheet to make a matrix I can draw data from to make the graph in dashboard. (this is where the formula is going to be used).
The picture below is the sheet I am referencing in the formula (service range 1). it is the date column on the left that is used.
I want the formula to count the dates if they are from a certain quarter in a certain year.

@Frederik Soerensen your date format in the formula will need to match the date column formatting on the source sheet. Your formula formatting is also incorrect as you are using ; instead of a , after the reference range.
Is the Opening Date on the Service sheet formatted as a date? It doesn't appear to be as it is not one of Smartsheets predefined formats

I will try to give a simple solution to minimize the load caused by complex formulas:
in the main database, sheet add the following helper column "Year", "Quarte" and use the following formula as column format formula on those columns:
1 Year =IFERROR(YEAR([OPENING DATE]@row), "")
2 Quarter =IF(AND(MONTH([OPENING DATE]@row) >= 1, MONTH([OPENING DATE]@row) <= 3), "Q1", IF(AND(MONTH([OPENING DATE]@row) >= 4, MONTH([OPENING DATE]@row) <= 6), "Q2", IF(AND(MONTH([OPENING DATE]@row) >= 7, MONTH([OPENING DATE]@row) <= 9), "Q3", IF(AND(MONTH([OPENING DATE]@row) >= 10, MONTH([OPENING DATE]@row) <= 12), "Q4"))))
the following screenshot shows the result
then you can use the following formula to count ( for example, 2018Q1)
=COUNTIFS({Service RangeYear}, "2018", {Service RangeQuarter}, "Q1")
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Hi, Bassam Khalil!
I'm looking for something similar but instead of one column with dates I have over 10. I want my formula to tell me how much money has been paid within fiscal years.
Here is an example of what I want. The last two columns are manual inputs, this is where I would like my formula.
Fiscal Years runs from July 1st to June 30th.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!