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
Best Answers
-
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.
Answers
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!