How to sum the first four cells is a row starting with the first non-blank cell?

I hope this is possible with a formula to avoid VBA. My data / table of values are in J to X. I want to get the sum of the 4 cells starting with the first non-blank cell. I believe a picture can describe the problem better.

excel-row-summation.png

I hope the image is attached at the top.
The goal is for Excel to identify the first non-blank cell, then sum the 4 rows starting with the non-blank cells regardless if the next 3 cells are blanks or not. I have search the forum and Google and most of the formulas are adding non-blank cells in a row.

I hope you can help.

Thank you.

Formula:

Please Login or Register  to view this content.

I posted a response in your VBA thread that works...

http://www.excelforum.com/excel-prog...ml#post4316337

Formula:

Please Login or Register  to view this content.

Originally Posted by bebo021999

Try:
=IFERROR(SUM(OFFSET(J2,,,,MATCH(TRUE,J2:X2>0,0)+3)),0)
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Hi, thank you very much for your reply. I tried your array formula and it works perfectly when there are 4 or more cells in a row to SUM. But when there are less than 4, the result is incorrect.
For instance, if the first non-blank cell is in W, it means only 2 cells to SUM -- W and X. The result shown is incorrect.

Originally Posted by dosydos

Formula:

Please Login or Register  to view this content.

Thank you!!!! This formula is working!!!

Originally Posted by cantosh

I posted a response in your VBA thread that works...

http://www.excelforum.com/excel-prog...ml#post4316337

Formula:

Please Login or Register  to view this content.

Thank you!!! This is also working!!!!