Subtotal filtered data

Question

Level 1

21 points

Subtotal filtered data

Is there any way to sum data in a column that is filtered data in Numbers?

I have a time tracking spreadsheet that I imported from Excel. The one issue I have not been able to resolve is a subtotal for filtered data. The filter hides data, and a sum formula adds values whether or not they are hidden. In Excel I use the subtotal function to sum filtered data, but that formula is not available in Numbers. The data I am filtering varies from project to project, so it doesn't lend itself to categories. Typical tasks include "Prepare report," "Edit report," "Review case file," "Respond to email questions from attorney," and "Teleconference with attorneys." I can easily filter the data by selecting one or more of these entries, but I haven't yet found a way to sum the durations for the filtered set (not counting workarounds involving pasting the filtered cells and summing their contents).

MacBook Pro 15", macOS 10.14

Posted on Mar 16, 2019 11:01 AM

Reply

Answer 1

Best reply

Badunit

Level 7

25,532 points

Mar 16, 2019 9:44 PM in response to SGIII

From what I see, that SUM sums up only visible numbers. An entire column of a filtered table can be selected and only the visible values will be summed (and counted and averaged, etc.). So it should work for what the OP needs, though it isn't automatic like SUBTOTAL is in Excel.

Reply

Answer 2

SGIII

Level 8

35,252 points

Mar 16, 2019 10:23 PM in response to DMWallis

Another thing you might try is the Categories functionality in Numbers go to the right panel or in your menu choose, for example, Organize > Add Category for "Task" (if you've selected the the Task column). That functionality is highly flexible, and will enable you to slice and dice your time spent data in many different ways. More here.

SG

Reply

Answer 3

SGIII

Level 8

35,252 points

Mar 16, 2019 6:22 PM in response to DMWallis

You can extract all kinds of subtotals from your data using the SUMIFS function, something like this:

=SUMIFS(Table 1::D,Table 1::B,$A2,Table 1::C,B2)

=SUMIFS(Table 1::D,Table 1::B,$A3,Table 1::C,B3)

=SUMIFS(Table 1::D,Table 1::B,A4)

Substitute ; for , in the formula if your region uses , as a decimal separator.

Life will be easier if you use regular names for your tasks. However in a pinch you can easily get a subtotal by holding down the the command key and selecting the cells you need, for example all teleconferences with attorneys.

The sum appears below. You can drag that sum into a cell and the formula that derived it will appear in the cell.

SG

Reply

Answer 4

DMWallis Author

Level 1

21 points

Mar 18, 2019 2:08 PM in response to SGIII

> However in a pinch you can easily get a subtotal by holding down the the command key and selecting the cells you need...

That's just what I needed. I was looking for less work, not more; and this works fine. I didn't really need a formula, just an easy way to sum the totals. I can even skip some of the filters when there are only two or three entries.

Using categories would just have take more time than adding the values manually (in a separate program, tab or column), which was my default. This method shows the subtotal directly. I have to transfer the value manually to my invoice (a Word doc). Copying and pasting won't work, since two of my clients require time in tenths of an hour. (I'm not asking how to display decimal hours--I use that system only in the invoice.)

Thanks very much!

Reply

Answer 5

DMWallis Author

Level 1

21 points

Mar 18, 2019 2:09 PM in response to SGIII

> However in a pinch you can easily get a subtotal by holding down the the command key and selecting the cells you need...

That's just what I needed. I was looking for less work, not more; and this works fine. I didn't really need a formula, just an easy way to sum the totals. I can even skip some of the filters when there are only two or three entries.

Using categories would just have take more time than adding the values manually (in a separate program, tab or column), which was my default. This method shows the subtotal directly. I have to transfer the value manually to my invoice (a Word doc). Copying and pasting won't work, since two of my clients require time in tenths of an hour. (I'm not asking how to display decimal hours--I use that system only in the invoice.)

Thanks very much!

Reply

Subtotal filtered data

Similar questions