I can't find any equivalent of the Excel Pivot Table or Open Office Data Table features. I'd like to sum up my data grouped by values in various columns.

For example, if I have

A       B       C
red     left    2
red     left    3
green   left    4
green   right   4

I want to be able to get

        red     green   all
left    5       4       9
right   0       4       4
all     5       8       13

Assuming the tables are named Original and Pivot, the cells in Pivot are filled using this formula:

=SUMIFS(Original::$C, Original::$A, "="& $A2, Original::$B, "="& B$1)

i.e. sum values in Original::$C if

  • corresponding value in Original::$A = value in first column of Pivot
  • corresponding value in Original::$B = value in first row of Pivot

The 'all' values are just simple sums of those rows or columns.

| improve this answer | |

Pivot table simply do not exist in Numbers and as far as I know they have never existed. In previous versions however there was a feature called "Categories" which used to serve almost the same purpose, but unfortunately in the more recent versions many of the features has been removed and "Categories" is one of them. The only possible way I have found to emulate a pivot table was to use formulas to categorize different values and run an aggregate on the them. This technique is explained here in the following blog post: How to mimic pivot tables in Numbers 3

| improve this answer | |
  • 2
    Welcome to Ask Different! We're trying to find the best answers and those answers will provide info as to why they're the best. Explain why you think the link you provided will answer the question. Links can change and become outdated so we prefer the answers to not just be a link. See How to Answer on how to provide a quality answer. – fsb Jan 23 '17 at 20:31
  • 1
    Thanks for your comment. I will try to elaborate on that. – Reza Feb 18 '17 at 19:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .