Add previous cell ignoring hidden cells

[penfold1992]

i have a set of data but its currently got hidden cells because some of the data would be considered as "out liars".
if I do a SUM(K2:K170) 170 is the end of the data, it gives a result of over 500,000 when the data consists of mostly 1.x and 2.x so this value is out because of the hidden out liars.

also im making a new column which adds up the data as it goes ie. L5 = K2+K3+K4+K5 so i have set up L5= L4+K5

but again this data is getting ruined by values that i have already chosen to hide (because i set L2 and L3 up and auto populate the rest)

is there any way to exclude hidden data from SUM formulas and also auto populate excluding hidden cells?

EDIT: actually if there is a way to sum formula i can do the auto population this way:
SUM($K$2:K5) and as i auto populate it will keep adding more onto the K5 value and yeah... thats a way

[benishiryo]

hi penfold1992, are you looking for something like this:
=SUBTOTAL(109,K2:K170)

this will ignore any rows you've hidden. do upload a sample Excel file so that we can help you better. to upload, press "Go Advanced" beside the "Post Quick Reply" button & click on the paperclip icon