3

I have a table featuring some outcome (column B) versus the day it happened (column A):

enter image description here

and I would like to produce another table with the unique dates in the first column and the total outcome of the day in the second column, looking like this:

enter image description here

I want the aggregated table to update itself automatically as new data are added to the first table, and I therefore tried to do it using formulae but it evaded me.

Improve this question
1
  • As of Numbers 11.2, you can accomplish this using a Pivot Table.
    – Nick Chammas
    Commented Dec 27, 2021 at 17:10

1 Answer 1

Reset to default
2

The formula for the right column of your aggregate table is:

=SUMIF(Data::A,A1,Data::B)

The parameters to SUMIF are:

  1. The column of dates which you wish to group by.
  2. The individual date you wish to output the sum of (i.e. the cell to the left).
  3. The column which you are taking the sum of for the group.
Improve this answer
2
  • But how do you get the aggregate's first column? (Sort the input table by 1st column then for each row in the 2nd table pick the min that is greater than the previous row for each cell?) That's the hard part here from my point of view. That would be great if you included it.
    – cglacet
    Commented Nov 29, 2021 at 8:16
  • @cglacet - I think the problem is that Numbers doesn't support formulae that output a column of values (vs. an individual value). Otherwise, an ergonomic solution here would be to have a formula defining Aggregate::A as something like DISTINCT(Data::A). See also: apple.stackexchange.com/q/269875/12509
    – Nick Chammas
    Commented Dec 27, 2021 at 16:44

You must log in to answer this question.