# Return sum of one column if today's date is between A and B, etc

✭✭✭✭✭✭
edited 12/09/19

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

Tags:

• ✭✭✭✭✭✭

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]@row1, [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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!