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

JLC · 08/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

JLC · 08/09/19

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]))

Paul Newcome · 08/12/19

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.

JLC · 08/12/19

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.

Paul Newcome · 08/12/19

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.

Paul Newcome · 08/12/19

Side note:

Thanks for sharing instead of deleting. You never know if someone else may stumble across it and find exactly what they need.

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

Comments

Help Article Resources

Categories