I have some data in Apple Numbers spreadsheet in the following sample format.
1.A $2
2.B $1
3.A $1
4.C $1
Now, I would like to have a spreadsheet in the same file to be updated automatically (based on the info in the above table) in the following format.
1.A$ 3
2.B$ 1
3.C$ 1
Please help me how to do this?
Enter the data in a table named "Data" as shown (the table on the left):
The create a second table (which I called "Summary").
enter the categories in the first column, as shown, then use the formula as shown below...
select cell B2,
now type the equal sign
now copy or paste from here the formula
SUMIF(Data::B, A2, Data::C)
type the return key
shorthand for this is:
B2=SUMIF(Data::B, A2, Data::C)
select cell B2, copy
select cells B2 thru the end of column B, paste
Enter the data in a table named "Data" as shown (the table on the left):
The create a second table (which I called "Summary").
enter the categories in the first column, as shown, then use the formula as shown below...
select cell B2,
now type the equal sign
now copy or paste from here the formula
SUMIF(Data::B, A2, Data::C)
type the return key
shorthand for this is:
B2=SUMIF(Data::B, A2, Data::C)
select cell B2, copy
select cells B2 thru the end of column B, paste
Coder_sls wrote:
Hopefully Apple Numbers will come up with something like Pivot Table very soon.
Categories has some of the aspects of Pivot Tables, including the ability to obtain a set of distinct values from a column. Have you actually tried what Badunit describes above?.
SG
Coder_sls wrote:
I tried it.
However, I don't want to change formatting of the data table. It is one I will keep updating(i.e. number of rows will keep increasing). The summary table should provide the results as computed.
That is exactly what the two-table solution Wayne provided above does. It updates the summary as you add rows to the data table. Have you actually tried it?
The Categories approach does not change content. But it does change order and formatting (as of course do Pivot Tables). But you can use Categories to quickly extract distinct values from a column that can be pasted into the summary table where you have your SUMIF or SUMIFS formulas.
SG
Hi again Coder_sls,
Applying Categories to the 'Data with Categories' table,
and then editing the 'Data' table by inserting 99 in cell C6. The 'Data with Categories' table automatically updates.
Regards,
Ian.
Thanks that helps.
Is it possible to automatically populate column A in summary table based on contents in column B of data table?
This way, I need to work on Data table only and Summary table gets updated.
Populating column A in the Summary table based on contents in column B of the Data table would be easy is we had the UNIQUE function, or similar.
There are even techniques for creating something like the UNIQUE function from what are called Array functions.
Unfortunately, last time I tried, I couldn’t get Numbers to perform such a function.
Perhaps I was simply not sufficiently creative!
Maybe someone else can help us, here.
However, it would probably be best to ask that as a separate question to the community.
Add a new column to the data table:
enter the formula:
A2=IF(C2<>"",IF(COUNTIF($C$1:C2, C2)>1, "", MAX($A$1:A1)+1), "")
fill A2 down for all of column A
Then in the Summary Table:
enter the formula and fill down:
A2=IFERROR(VLOOKUP(ROW()−1, Data::A:C, 3, 0), "")
I have to interject my usual warning about a bug in Numbers that prevents sorting of tables that contain formulas with range references such as C$1:C2. The references get all screwed up in the sort. I am hopeful it will be fixed soon.
Thanks to everyone who chipped in.
Its not as easier as I was expecting it to be in MS Excel, especially with growing data.
Hopefully Apple Numbers will come up with something like Pivot Table very soon. That would simplify/automate a lot of stuff.
May be considered as a feature request.
"May be considered as a feature request. "
Wrong audience. Apple Support Communities is a venue where users of Apple hardware and or software can ask and answer questions regarding how to do things with that hardware and software. Apple leadership and members of Apple's software development teams rarely visit here and even more rarely participate.
"Feature requests" should be sent to Apple via the feedback channel, not sent to the users (who have no direct say in determining what features will be incuded in the software.
Your portal to the Numbers feedback channel is the Provide Numbers Feedback menu item in the Numbers menu.
Regards,
Barry
I tried it.
However, I don't want to change formatting of the data table. It is one I will keep updating(i.e. number of rows will keep increasing). The summary table should provide the results as computed.
If its possible without changing the input content/formatting, that will be useful.
Hi SG,
Wayne's solution has worked for the sample table. However, it doesn't on the actual data. Im still looking at what is it that Im missing in there, because I want it to be really setup to avoid daily grind.
Regarding your second query, categories approach changes the order and formatting. In my raw data table, it is in a particular order as per the date and I don't want to change that. What Im primarily looking for is not to disturb the raw data table, it will be the data entry point. A new table/sheet should be able to take in the data from the raw table and the processing is all done in the summary table. Copying and pasting every time isn't the way I want to have it.
Reg. your comparison point with Pivot table, there is an option to create all the processing/filtering in a separate sheet in there.
Im going to stick to Numbers for the time being, at least and try to figure it out. Any help is welcome. Hopefully, I will be able to get it working soon. Thanks.
Wayne : When Im trying the step in summary table in my actual table, the column in summary sheet is remaining blank. Is there something Im missing?
Thanks Barry for the feedback.
Sent the feature request in the suggested channel.
Well , posting representative data will certainly help. Posting screenshot of what you have will also help us help you
Another option would be the categories feature of Numbers. You can have it subtotal each category (or count, average, min, max, etc. by clicking on the gear). The first screenshot below shows what it would look like when expanded. The second is with it collapsed. New data may be entered in one of the "blank" category rows or you can turn off categories to enter data. One thing to be aware of is it sorts your table. If you turn categories off, your table will be in alphanumerical order, not the order it was before you turned categories on.
Data Analysis on Apple Numbers
