How to use countifs with dates.

Frederik Soerensen

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}; < 1-4-2018)

how do I make the formula work? (or is it because the date format is wrong)

Garrett Henke

@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

Bassam Khalil

Hi @Frederik Soerensen 

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.

Frederik Soerensen

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.

Garrett Henke

@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

Bassam Khalil

@Frederik Soerensen 

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, 2018-Q1)

=COUNTIFS({Service Range-Year}, "2018", {Service Range-Quarter}, "Q1")

egrillasca

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.