I cannot get a SUMIF equation to properly calculate between a date range.
My equation is the following:
=SUMIFS([June 2024]188:[June 2024]263, [Revised Budget]188:[Revised Budget]263, >="01/01/24", [Revised Budget]188:[Revised Budget]263, <="01/31/24")
The equation seems to not recognize the year in the Criteria, so all January dates it includes in the SUM without reference to the year.
These columns are setup as Text/Number, so I am unsure if that is causing an issue.
Answers
-
Your date columns are set as text/number type columns?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
That is correct
-
Is there a specific reason you are using text/number instead of date type columns for the dates?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Strictly due to the data in the document. It was easiest to keep the columns as text/number because of other information in the sheet. I did create a separate column that is setup as a date column and I am still having the same issue, but now it won't sum any thing.
-
You will need a date type column with a formula in it that converts the text string into an actual date value.
=DATE(VALUE("20" + RIGHT([Text Column Name]@row, 2)), VALUE(LEFT([Text Column Name]@row, 2)), VALUE(MID([Text Column name]@row, 4, 2)))
Then you can reference this date column in your formulas being sure to use a DATE function within them.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!