Subtotal Function Across Horizontal Cells

[ellistyle@gmail.com]

I have built a sheet with client accounts in cells A2:A5000. Starting in column E & F, I have monthly sales and gross profit for each client. E&F is December, G&H January, I&J Feb....AE&AF Jan'10.


After column A there are total sales & total profit columns.


I want to use the subtotal (109) function in these total columns and then hide columns to get a quarterly (or fiscal year or 6 month...) figure. I wrote the sales formula:


=subtotal(109,e2,g2,i2...)


Hiding columns does not change the total. I tried a subtotal (9) also just to make sure I wasn't mistaken.


Is it possible for a subtotal function to work across horizontal and non-consecutive cells?

 

[keymaster]

SUBTOTAL doesnt work across (it only works down). It is a row function, not a column function.


If possible, try transposing the data using paste special > transpose option. Otherwise you can use some check boxes (one for every month) and use the box status in a regular SUM or SUMPRODUCT formula to sum up the values to be shown. This assumes you will check/uncheck instead of unhide/hide columns.