Solved: IF HIDDEN....DONT SUM

[asingh]

Hi,

I have numbers in columns a,b,c. And in column d, I am summing [adding] the column contents. So cell d1 reads:

=SUM(A1:C1).

What I want is...suppose if I HIDE column 'b' then my sum should only be from the values in columns A and C. Can this be done using some standard Excel formulea.

I dont want to do it using macros.....! [Have all ready done that...!, now want to know if it is possible using standarnd spreadsheet functions..?]


regards,

Anil Singh

[matthewspatrick]

There is no native function that will do that. You would need to use VBA, and prob ably also to make the UDF volatile to have a fighting chance of it recalculating when you would want it to.

SUBTOTAL is a possibility: If the range you are evaluating includes rows that are hidden because of filtering, the hidden rows are omitted from the calculation...

[asingh]

Hi,

thanks for the reply....I am hiding...Columns..so the FILTER option with SUBTOTAL wont work..Well I did it using VBA..wanted to see if anyone knew any spreadsheet function.

Thanks anyways..for giving it a thought..

regards
asingh