How to use countifs with dates.

Options

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)

Best Answer

  • Garrett Henke
    Garrett Henke ✭✭✭✭✭
    Answer ✓
    Options

    @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

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    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.

    PMP Certified

    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"

  • Frederik Soerensen
    Options

    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
    Garrett Henke ✭✭✭✭✭
    Answer ✓
    Options

    @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
    Bassam Khalil ✭✭✭✭✭✭
    edited 06/10/21
    Options

    @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")

    PMP Certified

    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"

  • egrillasca
    egrillasca ✭✭✭✭
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!