3

I have a table that looks much like this:

enter image description here

That Category column is entered manually and will have several duplicated values.

I’d LOVE to be able to create a “Summary” table that manages to copy and manipulate some of this data. Namely:

  • It would take the “Category” columns with its duplicates, remove duplicated and sort the list;
  • Have a second column that sums all values in that category’s fields;
  • And finally, have a third row that shows how many times the original keywords appeared in the first table.

Here is a screenshot of what I’m trying to achieve:

enter image description here

Possible??

4

If your category column contains categories that are correctly spelled each time, then a simple solution would be to use the SUMIFS() and the COUNTIFS() formulas.

  • In the Summary table, add a category from the category column from the Finances table.
  • Next, in the Amount column of your Summary table, add the following formula: SUMIFS(2017 Finances::Amount,2017 Finances::Category,A2) Where A2 contains a unique category.
  • In the Count column, use this formula: COUNTIFS(2017 Finances::Category,A2)
  • Drag the formulas down the columns to fill for each category.
  • Sort Ascending or Descending as desired.

The SUMIFS and COUNTIFS compare the category column with the entry in the Summary table's entry. If there is a match, it sums or counts based on the category. Repeat as needed.

NOTE:

Dynamically creating a Summary table without knowing the categories is quite a bit more complicated as Numbers does not have Pivot Table (as Excel or Google Sheets has) functionality as of the date this answer was posted. Great suggestion for Apple Feedback.

See the illustrations below.


SUMIFS formula

Countifs formula

  • What is like Access but for mac? – Lisa Aug 14 '17 at 4:17
  • @Lisa FileMaker. $$$ but is a proper relational database unlike Numbers or Excel. OpenOffice had a database too IIRC. – bjbk Aug 14 '17 at 11:40
  • This is really fabulous and so minimal. Everything else I found and/or tried was several combinations of functions. Thank you! – Brandon Durham Aug 14 '17 at 15:09
  • Glad it works for you. Spreadsheets are great but they have their limits over a full blown database. But a database is overkill for this job. Vote up if you really want to thank me. ;) – bjbk Aug 14 '17 at 15:14

You must log in to answer this question.

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