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.
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