What formulas can I use for cumulative calculations in Numbers?

There are several formula-based ways to do it. Here are three and how they fair under different situations.

R2 =IF(D2=D1,SUM(Q2,R1),Q2)

Fill down

Used SUM rather than + in the formula so that it would work in row 2. The SUM formula ignores text.

• Adding a row in the middle: You will have to fix the column of formulas to include the new row
• Deleting a row: It will cause an error and miscalculations. You will have to fix the column of formulas
• Moving a row manually: You will have to fix the column of formulas
• If a date is out of place (like there is a 1/3/24 in the middle of the 1/2/24's): the cumulative will restart.
• Sorting: I think sorting will be okay with this formula

------

R2 = SUMIF(D$2:D2,D2,Q$2:Q2)

Fill down

• Adding a row in the middle: No problem
• Deleting a row: No problem
• Moving a row manually: You will have to fix the column of formulas
• If a date is out of place (like there is a 1/3/24 in the middle of the 1/2/24's): The formula will give you a cumulative number for this row that will be part of the cumulative for its date. It will just be out of place in your table.
• Sorting: do not sort the table. Numbers has a very long standing bug about sorting formulas that have references like D$2:D2 where the first part is locked to a row and the second is not. It creates reference errors.

------

Make a new column (say it is column S)

S2 = ROW()

R2 =SUMIFS(Q,D,D2,S,"<="&S)

Fill down with both formulas

It requires that extra column, which is a downside.

• Adding a row in the middle: No problem
• Deleting a row: No problem
• Moving a row manually: No problem
• If a date is out of place (like there is a 1/3/24 in the middle of the 1/2/24's): The formula will give you a cumulative number for this row that will be part of the cumulative for its date. It will just be out of place in your table.
• Sorting: No problem

----

For the most trouble-free result, the third formula is best but the others will work as long as you know their limitations.

Make sure your dates in column D are actually just the date (at 12:00AM). None of these formulas will work correctly if any of the dates have a time associated with them (other than the default 12:00AM).

Maybe this is what you're looking for.

Put this formula in E2 and drag it down

=SUMIF(A$2:A2;A2;C$2:C2)

The first two very simple solutions that come to mind are:

A subtotal at the bottom of column C and with the quick filter choosing the number of the day. This way you can see the sums day by day.

=SUBTOTAL(9;Net Sales Amount)

Or using a sum.if in E2 that you then drag down

=SUMIF(Units Sold;RIGA($B1);Net Sales Amount)

Have you considered a Pivot Table? They're designed for summarizing data like this.

It's three clicks to get you what you want. Select your table, then:

Organize -> Create Pivot Table

In the Inspector, drag Sales Date to the Rows field

Drag Net Sales to the Values field.

Done:

If you really want a cumulative running total then set R2 to:

=Q2

(since the first cumulative total is the same as the first sale on the first day)

Then set R3 to:

=IF(D3=C3, R2+Q3, Q3)

Fill down.

Breaking down that formula, it checks to see if the Date of this row (D3) is the same as the date in the preceding row (C3). If it is, then it adds the previous cumulative total and the current row's net sales value. If the dates don't match then it assumes a new day and simply copies the first sale of the day (note this works on the days being consecutive and not out of order)

The main issue I see with doing it this way is the lack of visual cue as to when the day changes, but it's your preference.

If you want to see the change of the day you can use the conditional highlighting in column R. As a rule equal number R1 to cell Q1 and then combine the rule on the entire column and every change of day will be highlighted.

In your screenshot the formula must be put in R2