Handling data in Numbers

Question

Handling data in Numbers

I have table with several columns. I want to categorize this data and the filter some lines (not have them included). So I use Categories, which works fine. I then filter the data per my needs. Also works fine after a couple of tries.

but then I want to summarize the remaining data. So I try the feature in Categories with sub-totals. It gives me a total but quickly I realize that it is not the value corresponding to the sum of the values I see in my table (after filtering). So I do a quick test and use the function SUM and mark all the lines in my table. The I get a different value which still is not the correct sum of lol the remaining lines.

can this be true? Or is it a bug?

and if not a bug, what is the explanation?

nd how do I perform this simple (?) task in filtering values and summarizing the remaining ones?

iPad Pro 11-inch Wi-Fi

Posted on Oct 25, 2020 2:58 AM

Reply

Answer 1

Best reply

Badunit

Level 7

25,994 points

Oct 25, 2020 12:56 PM in response to JonasWaterman

Design choice and compatibility with other spreadsheet apps. Hiding a row may remove it from view but does not remove it from the table. If a function includes a hidden cell, it uses the hidden cell.

Coincidentally, I was just researching Excel's AGGREGATE function yesterday for a different question (I had never heard of it before) and this is one of the things it allows you to do: ignore data in hidden rows. It is not available in Numbers.

Regarding your SUM not coming out to what you expect, is it possible that there are rows before 130 and/or after 349 that are being included in the Category total but not your SUM? Your SUM only includes 130 through 349.

Reply

Answer 2

Oct 25, 2020 5:02 AM in response to Yellowbox

ok, so this is an example of a table without the filters and categories. If I add a Category for Type A I will get two sections; one with Yes values and one with No values. Then if I filter on Type C, Green I will not see any other values besides the lines with Green. All good! But if I then add sub-totals to my Category it will show 104 for the Category Yes filtered on Green. But that is not the value I am looking for. I thought that it would give me the total of the two lines corresponding to Yes and Green (which is 10 and 11).

I hope this explains better.

Reply

Answer 3

Oct 25, 2020 5:20 AM in response to JonasWaterman

I also add my original table with Categories, sub-totals and Filters included. As can be seen the subtotal is 6982643.

When I do this calculation in a separate table and make a simple SUM calculation I get the sum 6179072. So a different value ...

And none of them is of course equal to the filtered list.

Reply

Answer 4

Yellowbox

Level 7

20,383 points

Oct 25, 2020 4:53 AM in response to JonasWaterman

Hi Jonas,

I do not understand how you are using Filter and Categories. A screen shot of your data table (without Filters or Categories) will help us to see what you see.

Delete or hide personal or sensitive information before you take the screen shot.

Regards,

Ian.

Reply

Answer 5

Oct 25, 2020 7:45 AM in response to Yellowbox

Thanks. Yes that might work. i will try.

But I still find it strange that the function of Categories and Filter does not work with sums. That must be a bug, or is there any logic that explains the existing functionality?

Reply

Answer 6

Yellowbox

Level 7

20,383 points

Oct 25, 2020 7:39 AM in response to JonasWaterman

HI Jonas,

Instead of filters and categories perhaps the SUMIFS function will work for you.

Formula in the 'Result' table C2 =SUMIFS(Data::B,Data::A,A2,Data::C,B2)

Fill the formula down.

Regards,

Ian.

Reply

Handling data in Numbers

Similar questions