Can somebody help me please with formulas - a new user to Apple Numbers
Below is a rough example of my spreadsheet, name and amount - what I want to do is have a separate sheet showing the name and total for each name. Further, when new items are added for that name it will update the sum automatically !!!
DMEHMED 2
HOWARD64RC 3
HOWARD64RC 1
DMEHMED 3
TIMOTHGUN4 1
DMEHMED 1
DMEHMED 2
iPad Pro, iPadOS 26
Assuming your data is in a table containing two rows, this is pretty easy to do:
Table 1 (the data):
Create a new table (on the same sheet or a different sheet... wherever you'd like).
For this table, set cell A1 to:
=UNIQUE(Table 1::A,FALSE,FALSE)
This function looks at all the values in Column A of Table 1 and extracts the unique values.
If you prefer, you can also sort this list:
=SORT(UNIQUE(Table 1::A,FALSE,FALSE),1,1,FALSE)
So now the list is alphabetical.
Table 2 has to have enough rows to fit all the unique values.
Then, in Table 2::B1, set the formula:
=SUMIF(Table 1::A,A,Table 1::B)
This tells Numbers to take all the values in Table 1::A and compare them to the values in this table's column A. For any match, add the values found in the corresponding row in Table 1::B
Done:
Assuming your data is in a table containing two rows, this is pretty easy to do:
Table 1 (the data):
Create a new table (on the same sheet or a different sheet... wherever you'd like).
For this table, set cell A1 to:
=UNIQUE(Table 1::A,FALSE,FALSE)
This function looks at all the values in Column A of Table 1 and extracts the unique values.
If you prefer, you can also sort this list:
=SORT(UNIQUE(Table 1::A,FALSE,FALSE),1,1,FALSE)
So now the list is alphabetical.
Table 2 has to have enough rows to fit all the unique values.
Then, in Table 2::B1, set the formula:
=SUMIF(Table 1::A,A,Table 1::B)
This tells Numbers to take all the values in Table 1::A and compare them to the values in this table's column A. For any match, add the values found in the corresponding row in Table 1::B
Done:
> CAMELOT - thanks for your detailed explanation but I am still having trouble - the original spreadsheet is named as the 2nd one will be - rather than typing Table 1 do I type the name ?
A full reference takes the form:
=Sheet Name::Table Name::Cell(s)
For any reference, Numbers will accept the shortest definition, so if you just give a cell (or range) reference (e.g. A3, B:C, 12:14, etc) then numbers will assume you mean 'on this table'.
If you include a table name, such as Table 1::A3, Numbers will first look on the current sheet for a table with that name and use the cell reference there.
If the current sheet has no table of that name, it will look in other sheets for a table with that name. This means that 'Table 1' could be on a different sheet, as long as there is only one 'Table 1' anywhere in the document.
If you have two tables with the same name but on different sheets, then you need to prepend the sheet name so that Numbers knows which table to reference.
=Sheet 1::Table 1::A:A
=Sheet 2:Table 1::B12
So here there are two tables called 'Table 1', and the sheet name is prepended so that Numbers knows which one you want.
Does that help?
If you're ever unsure, know that you can also just click to select the cells/ranges... anytime you're entering a formula and the formula calls for a cell, you can click/drag on the range, even if you have to switch to a different sheet to do so.
CAMELOT - thanks for your detailed explanation but I am still having trouble - the original spreadsheet is named as the 2nd one will be - rather than typing Table 1 do I type the name ?
Numbers: Auto-sum by name on separate sheet