Excel - roundup then subtotal?

Question

A colleague has an array of values in "X4:X38". Since these are in a table which may be filtered, she wants to use the subtotal function to sum them - but wants all of the values to be rounded up first.

={SUM(ROUNDUP(X4:X38,0))}

works perfectly well. However,

{SUBTOTAL(9,ROUNDUP(X4:X38,0))}

Generates a generic "The formula you typed contains an error" message. I have tried various obvious things, like putting additional brackets around the "roundup" section, etc.

Any help would be appreciated.

I will take a look shortly but I believe this might not be possible with pure formulas (unless you want to use a helper column, in that case it would probably be pretty easy). I recall personal experiences being frustrated with the limitations of `SUBTOTAL` in the past. It may be possible with VBA, however. — ImaginaryHuman072889, Nov 08 '17 at 15:48

barry houdini · Accepted Answer · 2017-11-08T16:18:37.530

1

You can do this without a helper column by using this formula:

=SUMPRODUCT(SUBTOTAL(2,OFFSET(X4:X38,ROW(X4:X38)-MIN(ROW(X4:X38)),0,1)),ROUNDUP(X4:X38,0))

OFFSET effectively breaks the range down in to individual cells which are passed to SUBTOTAL function and that returns an array of 1 or 0 values based on whether each cell is visible after filter or not - this array is multiplied by the rounded values to give the overall sum of the rounded visible values.

Another way is to use AGGREGATE function like this

=SUMPRODUCT(ROUNDUP(AGGREGATE(15,7,X4:X38,ROW(INDIRECT("1:"&SUBTOTAL(2,X4:X38)))),0))

Given the complexity a helper column might be the preferable approach

edited Nov 08 '17 at 16:18

answered Nov 08 '17 at 16:07

barry houdini

45,615
8
63
81

Clever solution. Unfortunately it needs to use volatile functions so I agree the helper column might be better just to keep it simple. Still, great answer. +1 – ImaginaryHuman072889 Nov 08 '17 at 17:12
With the proviso that this is a volatile function as mentioned, this is a very clever answer and marked as answer. Thanks. Quite surprising that this functionality not included in SUBTOTAL - surely this can't be that unusual a situation? – Statsanalyst Nov 08 '17 at 17:48
@Statsanalyst I agree the `SUBTOTAL` function is severely lacking features. Typically if I want to do any sort of complex function with filtered data (only performing calculations on the non-hidden data), I just give in and use helper columns in conjunction with `SUBTOTAL` because as demonstrated, it is sometimes possible to do without helper columns but may be inefficient or overly complicated if your spreadsheet contains a large amount of data. – ImaginaryHuman072889 Nov 08 '17 at 18:29

score 0 · Answer 2 · answered Nov 08 '17 at 15:56

After investigation, looks like this is not possible without helper column.

Add a helper column which rounds the individual values in column X, e.g. type the following formula into cell Y4 and drag down to Y38:

= ROUNDUP(X4,0)

And then instead of

= SUBTOTAL(9,ROUNDUP(X4:X38,0))

use:

= SUBTOTAL(9,Y4:Y38)

Then if necessary you can just hide the helper column. Of course the helper column doesn't have to be column Y, it could be any column, e.g. a column far to the right of where the data ends.

Excel - roundup then subtotal?

2 Answers2