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
Check out the Formula Handbook template!