Help with summing prior four non zero values please?

Hello, can anyone help please?
I have a column full of data, generally numbers from 1 to 10, but with a lot of zero's.
Sometimes their could be more than 20 consecutive zeros. Can someone give me a formula for summing the previous four non zero values? The four non zero values could be in four cells, but it could be several before 4 non zero values occur.

I have attached an example- I hope that explains better what I can't work out

Thank you, Christopher

Type =if(A1=0,"",A1) in to cell C1 and drag formula in C1 down. Next, type =SUM(C1:C19) in to cell C20.

Hang on, don't think I read your Q properly, gimme a few minutes

Tricky one, isn't it?

Originally Posted by ChristopherHac

Tricky one, isn't it?

Has this stumped everyone- or have I explained wrongly?

Originally Posted by ChristopherHac

Has this stumped everyone- or have I explained wrongly?

Can anyone even give a pointer?

Just trying to jump up the list a bit

If you can work with a VBA solution, here is one:

Create a new module in VBA and paste this in:

Please Login or Register  to view this content.

Now use the formula

Formula:

Please Login or Register  to view this content.

instead of A10, just use the row where you are now for example, so when you drag down, A10 will change to whatever row you're on, but $A$1 will stay the same.

Hope this works for you.
Duncan

Thank you so much- got it all sorted- Christopher

Wow, I love this site, humble people with big intellects helping out those less knowledgeable !!!

Originally Posted by ChemistB

Standing on the shoulders of giants; with shg's formula, in B4 of your example

=IF(COUNT(IF($A$1:A4<>0, $A$1:A4,""))>=4, SUMIF(INDEX($A$1:A4, ROWS($A$1:A4)):INDEX($A$1:A4, LARGE(ROW($A$1:A4) * ($A$1:A4 <> 0), 4) - ROW($A$1:A4) + 1), "<>0"), 0) entered with CNTRL SHFT ENTER

copied down gives you exactly the same results as your example.

Thank you, works well

Agreed- if the world could be run by excel experts!!