Sum column from last value in column up to and including first zero

English
#1

I would like to be able to sum column O, from bottom to top, if the last value in the column is non zero, working up to the first zero column

In my attached example, I just want to sum cells O6 and O5.

If O6 was zero, then I want the sum value zero.

Solved.

my original coding for the Gas OB cells was

=IF(LEN(K6)<>0,IF(U5=“Y”,SUMIFS(gascredit,doi,">="&K5,doi,"<="&L5)-SUMIFS(gascost,doi,">="&K5,doi,"<="&L5)),"")

I altered this to

=IF(LEN(K6)<>0,IF(U5=“Y”,SUMIFS(gascredit,doi,">="&K5,doi,"<="&L5)-SUMIFS(gascost,doi,">="&K5,doi,"<="&L5)+IF(O5<>0,O5)),"")

Works a treat

image description
image description1918×1044 151 KB

#2

(IMO) You cannot do that with acceptable efficiency without either

  • assuring a maximum range of rows in column O probably containing numbers to process
  • accepting to rely on user code.

In addition you should clearly state if the used range is assured to be contiguous.

#3

The values in this range can be different. It is the balance carried over from previous tariff, or zero if I change provider.

An idea I had, was to calculate the cell coordinates of the last value in range (if non zero) and then the last zero value cell coordinates in the range.

I could then just sum that range using INDIRECT

#4

You can do things like this. The problem is that looking for zeros in a complete column is an “expensive” thing. If you can assure for your column O e.g. a maximum used range of, say, $O$2:$O$1001 the problem that searching bottom up is not supported will remein, but less efficient means will be efficient enough nonetheless.

#5

(I reopened the thread because there wasn’t an actual answer.)

To make more clear what I meant, I attach this example. It demonstrates a solution by formulas based on standard functions. As delivered the complete column A is scanned for zeros. This causes recalculation to be rather slow. restricting the range significantly (to 1000 data rows e.g.) makes a striking difference.

#6

That is brilliant, thank you

The range on my sheet is minimal. A new entry each time my gas or electric tariff is renewed.

Will embed and see what the results are.

#7

Absolutely fantasic. Your help has been much appreciated. Does the job brilliantly.

#8

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.