sum group by in spreadsheet
December 1, 2010 9:34 AM Subscribe
How do I do a "sum group by" of data in osx numbers program?
I've got data like:
fred 10
fred 20
susie 3
fred 4
and I want results like:
fred 34
susie 3
the sql equivalent of select name,sum(sales) from data group by name
but I cant figure out how to do that type of operation in numbers spreadsheet. thanks!
I've got data like:
fred 10
fred 20
susie 3
fred 4
and I want results like:
fred 34
susie 3
the sql equivalent of select name,sum(sales) from data group by name
but I cant figure out how to do that type of operation in numbers spreadsheet. thanks!
FYI for pivot tables:
1) select your data
2) Data > PivotTable and PivotChart Report...
3) Don't worry about the options and just click Finish on the wizard popup.
4) In the 'Row' section (or column if you prefer) drop in the 'Name' column
5) In the 'Data Items' section drop in the 'Amount' column
6) profit!
posted by thisisnotbruce at 10:48 AM on December 1, 2010
1) select your data
2) Data > PivotTable and PivotChart Report...
3) Don't worry about the options and just click Finish on the wizard popup.
4) In the 'Row' section (or column if you prefer) drop in the 'Name' column
5) In the 'Data Items' section drop in the 'Amount' column
6) profit!
posted by thisisnotbruce at 10:48 AM on December 1, 2010
Response by poster: is that numbers you are talking about? I'm lost,dont see a "data" menu item anywhere. thanks!
posted by H. Roark at 11:24 AM on December 1, 2010
posted by H. Roark at 11:24 AM on December 1, 2010
thisisnotbruce's instructions are for excel. I don't think Numbers supports pivot tables, but I'm not sure. Open Office's equivalent to pivot tables are called "Data Pilots".
posted by jrishel at 11:45 AM on December 1, 2010
posted by jrishel at 11:45 AM on December 1, 2010
Best answer: 1. If you don't already have a header row with Name, Quantity, Favorite Color, etc create it. Open the Inspector window (cmd-option-I), click the 3rd tab, and then pull down the second icon in the Header and Footer area to create a header row. Then type in the appropriate header titles.
2. Hover over the Name column. You'll see an arrow signalling a dropdown menu. Click that and select "Categorize by this column" Tada!
3. Now you've got all these sub-header rows. Hover over the Quantity column in a subheader row. Another arrow for a dropdown menu. Click that and you'll see some operations you can perform on that column, including Subtotal. That's what you want.
You can do some other neat things like hide the Name column (which is redundant when you've got the Name subcategory), and collapse the subcategories so you only see summary data.
posted by adamrice at 11:48 AM on December 1, 2010 [1 favorite]
2. Hover over the Name column. You'll see an arrow signalling a dropdown menu. Click that and select "Categorize by this column" Tada!
3. Now you've got all these sub-header rows. Hover over the Quantity column in a subheader row. Another arrow for a dropdown menu. Click that and you'll see some operations you can perform on that column, including Subtotal. That's what you want.
You can do some other neat things like hide the Name column (which is redundant when you've got the Name subcategory), and collapse the subcategories so you only see summary data.
posted by adamrice at 11:48 AM on December 1, 2010 [1 favorite]
Response by poster: this is perfect, thanks. how do you collapse the subcategories? As an excel user im still struggling with numbers - nothing is intuitive for me, yet.
posted by H. Roark at 12:05 PM on December 1, 2010
posted by H. Roark at 12:05 PM on December 1, 2010
See those disclosure triangles on the left? Click those. Not sure if there's a way to get all of them to open/close at once.
posted by adamrice at 12:46 PM on December 1, 2010
posted by adamrice at 12:46 PM on December 1, 2010
This thread is closed to new comments.
=SUMIF(A1:B4,"fred",B1:B4)
Repeat for susie.
If you don't want to have to manually specify all the different categories of freds and susies, pivot tables are where it's at.
posted by thisisnotbruce at 10:44 AM on December 1, 2010