# 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.

Tags:

• ✭✭✭✭✭

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!

• ✭✭✭✭✭

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.

• ✭✭✭✭✭

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!

• ✭✭✭✭✭

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!