7

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
Kevin Peterson
  • 462
  • Apparently there is a categories feature that gives some of this power, but I'm still looking for a better answer. http://macmost.com/pivot-tables-in-iwork-09-numbers.html – Kevin Peterson May 26 '12 at 00:56
  • 1
    What led you to think pivot tables are implemented in Numbers. It's not there AFAIK. – bmike Apr 13 '13 at 20:33

2 Answers2

5

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.

grg
  • 201,078
kharisma
  • 59
-2

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

Reza
  • 1
  • 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 [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