Numbers - Formula to return values based on specific dates

Question

Level 1

5 points

Hey I've tried googling and even some Chat GPT but haven't found the solution I'm after.

It seems easy in theory but I just can't get the formula I'd need to make it work.

Basically I have Column A with dates written like "01 Sep 23" with varying months and dates.

In Column B I have $ values.

I'm trying to create a formula that will calculate all the different month values separately and return the total amount for each month.

For example Septembers Total is $x. Octobers total is $x. etc.

Is this possible with Numbers?

Posted on Aug 14, 2023 3:01 AM

Reply

Answer 1

⚠️

Top-ranking reply

Level 4

1,627 points

Before Rockondevil reports that the formula raises a syntax error, here's the corrected version:

in cell D2:

=SUMIFS($B,$A,">="&C2,$A,"<="&EOMONTH(C2,0))

It also makes it copyable without modifications.

Edit: better include the year in entering the months to confirm which year the month is in, if you do this exercise for many years.

Reply

Answer 2

Level 8

36,596 points

You can do this in less than a minute with a Pivot Table. No formulas! Just click and drag.

Click in the data table and from the menu choose Organize > Create Pivot Table > On Current Sheet.

Then in the Pivot Options pane at the right drag the Fields down into the boxes below as shown in the screenshot.

That's all there is to it!

SG

Reply

Answer 3

Level 2

201 points

It is possible using SUMIFS and EOMONTH formulas:

Just create it for each month and adjust a range of cells

SUMIFS(B2:B8;A2:A8;">="&DATE(2023;8;1);A2:A8;"<="&EOMONTH("01 Sep 2023";0))

Reply

Quick Links