Sum Function

[happyhungarian]

Hi,

Not sure if this is possible but I need to create a dynamic sum function where the final row being summed is determined by when the function runs into a blank cell. For Example, if in cell A1 I have the following function =sum(A2:A10) and I have data points in cells A2:A10... but then my data set changes and now my data points only reside in A2:A6 I want the sum function to then only look at A2:A6 (because A7 is blank). I know it doesn't make sense why I need this and I should just sum all of Column A but then the function would capture additional data that I don't want it to. Is there a way to do this WITHOUT macros/VBA?

Thank you,

Jesse

 

[Matty]

Hi,

Try this array formula:

=SUM(A2:INDEX(A2:A100,MATCH(TRUE,A2:A100="",0)-1))

Which needs committing with CTRL+SHIFT+ENTER.

Matty

 

[Scott Huish]

Also:

=SUM(OFFSET(A2,,,MATCH(TRUE,A2:A100="",0)))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.