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

Find more posts tagged with

Formulas

Comments

JLC

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

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

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

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

Side note:

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

Sign in to join the conversation!

Quick Links

Trending discussions

Dropdown list restrictions
Is there a way to limit a dropdown list so that the values in the dropdown list can only be used once in a column?
seeking VLOOKUP or equivalent, search for most recent value, multiple dropdowns
My product/use case: I seek to build a camera inventory. Each camera has a unique identifier. Cameras are mobilized to different locations on different days. I need help making a VLOOKUP or equivalent combination of formulae functions to run 2 queries cross-referencing 2 grids, searching a column with multiple dropdown…
Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet