Ask Your Question
0

Consecutive Cells from column

asked 2017-05-02 21:43:52 +0100

ForcedLibreUser gravatar image

updated 2017-05-02 22:46:36 +0100

Hello, I am looking for an equation that will give me the total of consecutive cells...

image description

Basically I am looking for the formula in the Count Column (G) to return the same manual entered totals in (F). Column (F) will be deleted if I can get the formula to work...

I currently have =SUBTOTAL(9,E2:E$10000)*2-SUM(E2:E$10000) but that is only returning "0". Any ideas? Thanks in advance!

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2017-05-03 00:49:11 +0100

Lupp gravatar image

Since the function SUBTOTAL is only useful when applied to filtered ranges I will not comment on the formula posted in the OQ.

To get the number of cells of a contiguous sections filled in a column by formulas, the MATCH function should be the means of choice. However, matching is always done top down. Therefore it is much more effcient to return the result to the topmost row of any section than to the bottommost.
This statement applies mainly if the result is expected to be returned by a single formula.

Even more efficient and much more flexible are solutions using a helper column. In addition you can avoid this way formulas using ranges reaching far down and needing a value for the last row.

You may study this attached example.

edit flag offensive delete link more

Comments

Thank you very much, Im new to Libre Office and found the SUBTOTAL function and was trying to force it to work. Thanks again, your right a Helper column will be much more useful. Thanks

ForcedLibreUser gravatar imageForcedLibreUser ( 2017-05-03 01:54:45 +0100 )edit

BTW: Who did force you to use LibreOffice?
See also: https://en.wikipedia.org/wiki/Amor_fati

Lupp gravatar imageLupp ( 2017-05-03 12:37:34 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-05-02 21:43:52 +0100

Seen: 114 times

Last updated: May 03 '17