Dynamic cell reference in a formula

[Carrie]

Hello,

I have a column of data that I sum, provided the values in the cells are
greater than 100. Also, there are some rows that I always take out of this
sum. My problem comes when I want to insert/delete rows... how do I keep my
reference to the cells that I always take out of my formula without having to
manually go in and change each cell reference? Is this even possible?

Muchas gracias!
Carrie

 

[Bob Phillips]

the range can be dynamically referenced with

=SUM(A1:OFFSET(A1,,,COUNTA(A:A)))

as an example.

 

[John]

Assuming your numbers in column A the formula

=SUMIF(A:A,">100",A:A)

should total everything over 100. You will need to then subtract the numbers
you mention but so long as these are not ranges, e.g

=SUMIF(A:A,">100",A:A) - A2-A4

as opposed to ...

=SUMIF(A:A,">100",A:A)-sum(A1:A3)

then inserting / deleting rows should make no difference UNLESS you delete
rows that you subtract which will give you a #REF! error

HTH
John