CountIFs between two dates
I have been tasked with creating multiple sheets that feed into reports. In going about that process the sheets are populated by Project, Customer Issues and Roadmap sheets. Currently, I am trying to count how many Customer Issues were opened up between specific dates (Q1, Q2, Q3, Q4). I currently have the sheet working with adding additional columns that are pulled from the sheets, but it will be an issue to maintain moving forward. I am trying to simplify the calculations by trying to get this formula to calculate the same items without the other columns. Below is the formula that I am currently trying and getting a number of 0 when it should be 9 as that is what it is pulling using the additional columns.
=COUNTIFS({Customer Issues List Range 3}, <=DATE(2019, 1, 1), {Customer Issues List Range 3}, >=DATE(2019, 3, 31))
I must be something, but for the life of me can't figure it out.
Thanks,
Dan
Answers
-
I think you just need to reverse the >< in your formula, currently it reads...
<=DATE(2019, 1, 1) less than or equal to 1/1/2019
Try this:
=COUNTIFS({Customer Issues List Range 3}, >=DATE(2019, 1, 1), {Customer Issues List Range 3}, <=DATE(2019, 3, 31))
-
The small things in life matter the most. Thanks for catching that. It is now working. I appreciate the help.
-
I have a related, but slightly different issue. I have an "old formula" that worked for me before the introduction of @row and @cell, and I have been trying to convert the formula below to use @row instead of a row number range, but everything I try either returns "0" or an formula error message.
Basically, I want to count entries by fiscal quarter, so I'm trying to use a date range of ">=" and "<=" to calculate the number of entries (in the case below) for fiscal quarter 1.
If someone could give the formula below a look and suggest a solution I would be incredibly appreciative. Thanks in advance.
=COUNTIFS([Payment Received Date]1:[Payment Received Date]103, >=DATE(2019, 10, 1), [Payment Received Date]1:[Payment Received Date]103, <=DATE(2019, 12, 31))
-
Instead of using @row or @cell, it sounds like you're just looking to reference an entire column as your range, is that correct? If so, you'll just need to remove any row reference!
Like this:
[Payment Received Date]:[Payment Received Date]
Try:
=COUNTIFS([Payment Received Date]:[Payment Received Date], >=DATE(2019, 10, 1), [Payment Received Date]:[Payment Received Date], <=DATE(2019, 12, 31))
See: Create a Cell or Column Reference in a Formula
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Thank you Genevieve.
Worked! :-)
-
I'm trying to do the same thing although I am still getting a "0" instead of an actual count. I'm trying to count the requests come in each week and my current formula is:
=COUNTIFS({MaxEnroll Support Request Tracking Range 3}, >=DATE(2022, 6, 12), {MaxEnroll Support Request Tracking Range 3}, <=DATE(2022, 6, 18))
I have changed the date range, tried swapping the <> symbols and no matter what I do I still get zero however my sheet definitely has dates in the ranges i am inputting. The column it is requesting the date from is only a date but it is in 06/05/2022 format. does that matter?
-
Hi @Alywilk
This looks set up correctly to me, and the date format in your sheet shouldn't matter (as long as it's a date column). Is there any possibility that your "Range 3" is looking at the wrong date column?
Would you be able to post a screen capture of that column?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Genevieve P. I'm trying to count all of the tasks assigned to each person between dates and I need it to update automatically. We want to count between Tuesday and Monday of the following week. I have helper date columns added to update automatically in my sheet. I am using a metrics sheet and then the screenshot below is the sheet I am referencing. I have tried using this formula = COUNTIFS({Who}, @cell = "Angela Foldi", <=DATE({Monday's Date}) and when I do this I get the incorrect argument notice. I know I'm missing something here but not sure what it is.
-
Hi @Angie F
What does the sheet look like where you have the formula? Do you have all the Contacts listed down one column, and do you have a Date cell that you can reference for the "Monday" date and the "Tuesday" date?
The structure of a COUNTIFS is that you list the {Range} first then the "Criteria" for each matching pair of column and criteria:
=COUNTIFS({Column 1}, "Criteria 1", {Column 2}, "Criteria 2")
For your instance, you would want to look in the Date column for greater than a specific date and less than another specific date, so you'd list the date column twice. Something like:
=COUNTIFS({Who}, [Contact Column]@row, {Monday's Date}, >=[Monday Date]@row, {Monday's Date}, <=[Tuesday Date]@row)
This assumes you have the date listed in a column called [Monday Date] in your current sheet, and the Tuesday date listed in a column called [Tuesday Date]. Otherwise, you could type in the actual date into the formula:
=COUNTIFS({Who}, [Contact Column]@row, {Monday's Date}, >=DATE(yyyy,mm,dd), {Monday's Date}, <=DATE(yyyy,mm,dd))
Does that make sense?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Genevieve P. I have my formula in a metrics sheet and I am referencing the sheet I sent the screenshot for so I need a formula that works across multiple sheets. Here is the task list sheet.
This is my metrics sheet:
I also have a sheet where completed tasks are automatically moved to and I am referencing it as well.
-
@Genevieve P. I have played with the formula some more but it's still not working properly. There are 12 tasks to be done between the dates but I am getting a count of 0. Here is the formula. Our task list starts on Tuesday and ends on Monday.
-
Hey @Angie F
Thank you for the screen captures, this helps a lot! 🙂
What I would recommend doing is have a Date Column in this sheet, the Metrics sheet, that houses the Tuesday date and the Monday date. That way you can reference a specific date in your formula to look between:
=COUNTIFS({Who}, [Contact Column]@row, {Monday's Date}, <=[Monday Date]@row, {Monday's Date}, >=[Tuesday Date]@row)
The [square references] are references to dates in the current sheet, versus dates in a second sheet. Does that make sense?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Genevieve P. It makes sense. I will see what happens when I try it like this. Thanks!
-
Hey @Angie F
Since formulas with dates can be a little complicated, what about using a Report instead? You could filter the report by the dates wanted, then Group it by the Assigned To column to see a Summary of each person's number of tasks that week. You can even put this type of report into a Dashboard either as a Report widget or a Chart widget.
Just some thoughts on how to get around pesky formula errors!
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Genevieve P. the problem is the CEO and COO want to see everything in charts. A report would be way easier. I think part of the issue is with my record a date in my Monday column. The automation wants to record the wrong date. Maybe I need to look at putting in a formula instead of recording a date. What are your thoughts on that?
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives