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 selfupdating 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
 10.7K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!