Sum values between zeros in a column

[ghr2]

In cycling there is a GPX file (more often than not it comes with an altitude parameter). In attached file it is in 1st column (it measures meters above sea level).
2nd column show climbing, 3rd column is downhill (made using simple IF in Excel)
I want to calculate the length (sum) of a a current climb or "green area".
What Excel formula should I use to get the "green" sums (seperately).

I know this can be done in SQL (by adding some constant and then using simple "group by").
I can do this in imperative programming language (say C, but it takes time).
Is there a simple solution in EXCEL?

I then want to get to know top 10 climbs in the area (where GPX comes from).

[Kaper]

You probably can use simple SUMIF
like

Please Login or Register  to view this content.

Assuming the columns on a screenshot are A:C

[Fluff13]

Assuming your data starts in A1, then in B2 copied down
=IF(C2=0,A2-A1,0)

[ghr2]

Tried both formulas. Didn't do expected result. I attached original file (copy) with columns (A and B).
If we look at column C.

Here is manual calculation
C8 = SUM(B6 + B7+ B8)
C12 = SUM(B10 + B11 + B12)
C14 = B14

How to automate C8, C13, C14

[Fluff13]

Unfortunately I misunderstood what you are trying to do and what you need is beyond my knowledge of formulae.

[ghr2]

No problem.

The results will allow me to get TOP 10 climbs (sorted numbers in EXCEL simply as that).
I will add column C with some magic_number then import all 3 columns in SQL database and
use select sum... group by magic_number.

[joeu2004]

I would use a "helper column" (additional cells, which can hidden) to track cumulative sums. In your example, enter the following formula into D2 and copy down the column:

=IF(B2=0,0,B2+D1)

Then enter the following formula into C2 and copy down the column:

=IF(AND(D2>0,B3=0),D2,"")

-----
Unrelated observation.... Your use of SUM in SUM(B6 + B7+ B8) is superfluous and serves no useful purpose. Write either SUM(B6,B7,B8) or simply B6+B7+B8.

[MrShorty]

I don't know my way around SQL or C, so I don't know if this is easier in the spreadsheet programming language or not. It seems like, whatever programming language you choose to use, the program must do the same things (identify each "group", then sum for that group). Ultimately, the approach I would use feels something like the "group by constant" thing you describe in SQL. In a spreadsheet, I would probably go with some variation of:

1) identify groups. Enter 0 in C2. In C3, use a simple IF() function to increment groups =IF(AND(B2=0,B3>0),C2+1,C2).
2) Either a pivot table (https://www.excel-easy.com/data-****...ot-tables.html ) or SUMIFS() to sum column B based on the values in column C (https://support.office.com/en-us/art...6-611cebce642b ).

[63falcondude]

Try this in C2:

=IF(OR(B1<>0,B2=0),"",SUM(B2:INDEX(B2:B$1000,MATCH(0,B2:B$1000,0)-1)))

[Pete_UK]

I put this formula in D2, so that you can compare with the sums you already have in column C:

=IF(B2=0,"",IF(B3=0,SUM(B$2:B2)-SUM(D$1:D1),""))

If you want to have it in C2 you just need to change the column in the final SUM:

=IF(B2=0,"",IF(B3=0,SUM(B$2:B2)-SUM(C$1:C1),""))

It only produces the SUM at the end of each block (i.e. if the following value is zero) and it works by adding all the numbers in column B up to that point and then subtracts any numbers in column D (or C) up to the previous row. It took some time to calculate in my version, but it seems to produce the correct results.

Hope this helps.

Pete

[Glenn Kennedy]

Your thread title is kinda weak. We really expect them to resemble the sort of thing you'd use if you were using Google. Since you're new here, I have changed it for you. Please bear this in mind next time that you start a thread

[Pete_UK]

I keep forgetting that you are a Mod now, Glenn.

Pete

[ghr2]

I would use a "helper column" (additional cells, which can hidden) to track cumulative sums. In your example, enter the following formula into D2 and copy down the column:

=IF(B2=0,0,B2+D1)

Then enter the following formula into C2 and copy down the column:

=IF(AND(D2>0,B3=0),D2,"")

-----
Unrelated observation.... Your use of SUM in SUM(B6 + B7+ B8) is superfluous and serves no useful purpose. Write either SUM(B6,B7,B8) or simply B6+B7+B8.

Worked perfectly!

[63falcondude]

Glad we could help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[Glenn Kennedy]

Very often... so do I!!