Return sum of one column if today's date is between A and B, etc
Hi guys, I can get the following formula to work without issue:
=IF(TODAY() = DATE(2019, 8, 9), SUM([2019 Aug]:[2019 Aug]))
What I can't do is update the logical expression to be if today's date is between Aug 1 and Aug 31. Essentially I want a self-updating formula that will always show me the sum of the appropriate month's column based on today's date/month. I'll eventually nest everything to say if today is between Jan 1 and Jan 31, show the 2019 Jan column sum, if today is between Feb 1 and Feb 28, show the 2019 Feb column sum, etc. etc. etc.
Here's the formula I've written and tried modifying to no avail:
=IF(TODAY() <> AND(DATE(2019, 8, 1), DATE(2019, 8, 31)), SUM([2019 Aug]:[2019 Aug]))
Any suggestions?
Thanks,
Jaye
ETA: the error presented is #INVALID DATA TYPE
Comments
-
Found the answer by playing around more and thought I'd share instead of deleting.
=IF(AND(TODAY() > DATE(2019, 8, 1), TODAY() < DATE(2019, 8, 31)), SUM([2019 Aug]:[2019 Aug]))
-
While this is technically correct, there are actually a number of ways to make this easier for you. The most immediate is by removing the need to hard code whether a month has 28, 29, 30, or 31 days in it.
To do this you would reference the month and year using those two functions.
=IF(AND(MONTH(TODAY()) = 8, YEAR(TODAY()) = 2019), ..................)
.
Another option would be to create a new row at the top and indent everything underneath of it. You could then use a basic
=SUM(CHILDREN())
in each of the monthly columns to gather your total.
.
To have the current month pulled, you would use an INDEX statement to look across the parent row and use a MONTH statement to establish which column to pull from.
.
The syntax of an INDEX statement would be this...
=INDEX(range_to_pull_from, row_number, [column_number])
.
The range would be the parent row
=INDEX([Jan 2019]@row:[Dec 2019]@row, row_number, [column_number])
.
Since we are only looking at 1 row, we would use the number 1 for the row number.
=INDEX([Jan 2019]@row:[Dec 2019]@row, 1, [column_number])
.
And the column number would be the current month number.
=INDEX([Jan 2019]@row:[Dec 2019]@row, 1, MONTH(TODAY()))
.
There are actually quite a few more options, but these are just the first two that came to mind.
-
Paul, really appreciate your alternative methods provided. Always love hearing of more ways to do the same thing - certainly helps when building other formulas in the future! Thanks so much for chiming in.
-
No worries at all.
I deal with A LOT of dates in my sheets along with pulling date based metrics. I've learned a thing or two about making them easier to work with to avoid having to constantly edit the formulas themselves. It helps with consistency and accuracy (I have fat fingers on a keyboard lol) of data and makes my life MUCH easier when I can dragfill formulas instead of individualizing formula after formula after formula.
I figure every person that benefits from my hardship is one less person banging their head against a wall. Haha.
-
Side note:
Thanks for sharing instead of deleting. You never know if someone else may stumble across it and find exactly what they need.
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!