SUMIFS function with wildcard for dates

I think what you want to do can be done with wildcards if you add a new column to your table (which you can hide later). The wildcards work on text but not dates so add a column that converts the dates into text and use it for the SUMIF test.

If your dates are in column A, the formula would be a simple =A&""

Put it in every row so that, if/when you add new rows, it automatically populates in the new rows.

The downfall of using text vs using actual dates (including inside the SUMIF, either referenced from a cell or created with the DATE function) is that it will fail if the user's date system is not set the same as yours. Using actual dates is a more robust solution.

Hi zoooj,

I understand your reluctance to use extra columns to split a date into days, months and years. Here is a method that uses one extra column ("Month") that you can hide when all is working well.

Formula in Table 1 B2 =MONTHNAME(MONTH(A2))

Fill down.

In the Summary by Month table, format column A as Text and type January into A2. Fill down.

Formula in B2 =SUMIF(Table 1::B,A2,Table 1::D)

Fill down.

Sorry about the zero balance. These were made-up values.😉

Regards,

Ian.

Hmmmm…

Correct link? I got this message on clicking it.

The page you tried was not found. You may have used an outdated link or may have typed the address (URL) incorrectly.

Please visit the main page of Apple Support Communities for links to discussion areas.

Regarding: "I'll see if I can work out how to submit a feature request now!"

Numbers menu > Provide Numbers Feedback, then choose Feature request from the menu provided.

Regards,

Barry

Thanks, **!

I think I was I was in a bit of a rush when I looked at that earlier today, and didn't examine the link closely.

Certain sense of deja vu on looking back at that thread.

Regards,

Barry

The link I posted still had the period at the end, even though it isn't in the visible text. Must have been the way I copy/pasted/edited it.

https://discussions.apple.com/thread/250355392

zoooj wrote:

total incoming and outgoing funds per month.

If you're looking for a quick, simple, and flexible approach you might want to consider Categories (a.k.a. Smart Categories).

Let's say you have downloaded raw data something like this:

Click in the Date column, and from the menu choose Organize > Add Category for "Date". That will give you something like this:

Click a shaded cell in the Incoming column, then the "gear" symbol that appears next to it, and choose "Subtotal." Repeat in the Outgoing column. That gives you something like this:

Collapse rows if desired to hide detail.

Turn off Categories in the right panel when you want to see the whole table again.

This is all very fast and easy. Just a few clicks. No formulas to enter.

SG

The posted link had the period at the end of the sentence included in it.

https://discussions.apple.com/thread/250355392