Ask Your Question

Consecutive Cells from column [closed]

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

ForcedLibreUser gravatar image

updated 2020-07-28 21:11:13 +0100

Alex Kemp gravatar image

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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-07-28 21:11:32.580107

1 Answer

Sort by » oldest newest most voted

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


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:

Lupp gravatar imageLupp ( 2017-05-03 12:37:34 +0100 )edit

Question Tools

1 follower


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

Seen: 192 times

Last updated: May 03 '17