SUMIF Formula Based on Date of Month

Question

SUMIF Formula Based on Date of Month

Column A contains effective dates of MM/01/2023.

Column B contains money paid/owed as of MM/15/2023.

I need to create a SUMIF formula that totals the accumulated amounts in Column B that were/are owed as of Today's date.

So, for example, if Today is August 14, it would only total the months of June and July, but if Today is August 15th or later, it would total June, July, and August, and so forth.

Essentially, I need a self-updating total of all the amounts due as of the day the spreadsheet opens, but I am stuck with the format of the cells as they are shown below, meaning that I cannot have other helper columns.

iMac 21.5″ 4K, macOS 13.4

Posted on Aug 22, 2023 8:56 AM

Reply

Answer 1

jaxjason

Level 5

5,047 points

Aug 26, 2023 1:18 PM in response to mac_user_4ever

this is very similar to some other posts I have seen while browsing today... lots of budgeting questions...

You could use a SUMIFS to do what you are asking I think. Just use a condition with <= TODAY

In this example I used earlier, I added a test to dates as the last pair of arguments to test that the date column is earlier or equal to todays date. notice that it ignores line 4's food (Home) value since that date is after todays date (8/26/23)

Reply

Answer 2

Recycleur

Level 4

1,609 points

Aug 26, 2023 2:26 PM in response to mac_user_4ever

This is basically the same question you asked today about the rent ledger.

The total until the 15th of this month:

=SUMIF($A1:$A12,"<="&TODAY()−14,B1:B12)

To consider the amount in column B just subtract 14 days to today's date to compare to the date in the first column (which is the first of the month even though only the month name is shown, unless you specifically entered another day.).

If today is the 14th, then the subtraction results in the last day of the past month, so we don't add the amount for this month in column B. If we're on the 15th or after, then the subtraction gives a result equal to or superior to the first of the month.

Now I'm not sure the dates you show in your picture are really dates because they are left-aligned, unless it was forced like this in the Format / Text pane.

To be sure check the bottom-left corner of the window when you select a cell containing a date-looking value. Do you see a real date with numbers (don't worry about m/d/y or d/m/y or y/m/d), of just the same text than in the cell? If the latter, clear all cells with the month names and enter Jan 2023 in the first cell. Then grab the yellow dot at the bottom of the cell and drag it down to the 12th cell. It will fill all other months.

Reply