Count Between Dates

Hello, I need a formula that identifies how many rows I have per quarter. Example; Q1 from Jan 1st to Mar 31 of this year.


Some help Please

Tags:

Best Answers

  • Dakota Haeffner
    Dakota Haeffner ✭✭✭✭✭
    Answer ✓

    Hey Luis,


    Just something to try, you might need to do a few columns. One defining the Quarter with an IF statement like:

    =IF(ISDATE([Date]13), IF(MONTH([Date]13) = 1, "Q1", IF(MONTH([Date]13) = 2, "Q1", IF(MONTH([Date]13) = 3, "Q1", IF(MONTH([Date]13) = 4, "Q2", IF(MONTH([Date]13) = 5, "Q2", IF(MONTH([Date]13) = 6, "Q2", IF(MONTH([Date]13) = 7, "Q3", IF(MONTH([Date]13) = 8, "Q3", IF(MONTH([Date]13) = 9, "Q3", IF(MONTH([Date]13) = 10, "Q4", IF(MONTH([Date]13) = 11, "Q4", IF(MONTH([Date]13) = 12, "Q4")))))))))))))

    Then maybe have a metrics column with a =COUNT statement counting for Q1, Q2, Q3, and Q4.

    Hope this helps!

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

    If the sheet is only for a single year you can do the following:

    1) Create a helper formula column and name it Month #. The column formula will be =MONTH(Date)@row with Date being your date column. This will return the number 1-12 of each month.

    2) The formula will count how many rows are in a particular quarter (this example will count the rows associated with Q1): =COUNTIF([Month #]:[Month #], AND(@cell >= 1, @cell <= 3))

    To do the same with multiple years create a Year # helper column with the formula =YEAR(Date)@row and then use =COUNTIFS and then add the year to the end of formula.


Answers

  • Dakota Haeffner
    Dakota Haeffner ✭✭✭✭✭
    Answer ✓

    Hey Luis,


    Just something to try, you might need to do a few columns. One defining the Quarter with an IF statement like:

    =IF(ISDATE([Date]13), IF(MONTH([Date]13) = 1, "Q1", IF(MONTH([Date]13) = 2, "Q1", IF(MONTH([Date]13) = 3, "Q1", IF(MONTH([Date]13) = 4, "Q2", IF(MONTH([Date]13) = 5, "Q2", IF(MONTH([Date]13) = 6, "Q2", IF(MONTH([Date]13) = 7, "Q3", IF(MONTH([Date]13) = 8, "Q3", IF(MONTH([Date]13) = 9, "Q3", IF(MONTH([Date]13) = 10, "Q4", IF(MONTH([Date]13) = 11, "Q4", IF(MONTH([Date]13) = 12, "Q4")))))))))))))

    Then maybe have a metrics column with a =COUNT statement counting for Q1, Q2, Q3, and Q4.

    Hope this helps!

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

    If the sheet is only for a single year you can do the following:

    1) Create a helper formula column and name it Month #. The column formula will be =MONTH(Date)@row with Date being your date column. This will return the number 1-12 of each month.

    2) The formula will count how many rows are in a particular quarter (this example will count the rows associated with Q1): =COUNTIF([Month #]:[Month #], AND(@cell >= 1, @cell <= 3))

    To do the same with multiple years create a Year # helper column with the formula =YEAR(Date)@row and then use =COUNTIFS and then add the year to the end of formula.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!