CF from first value to last value including null or 0 values in between

[purdue7997]

I'm looking for a solution to a problem set I've been working on. I attempted to mimick a formula I found via Google but it's not working for my scenario (or I may be doing it wrong).

For each year row, I'd like to CF from the first value (greater than 0) to the last value including any zeros in between. The zero's outside of the first values are not to be CF.

For example, in the attached workbook...the year 2017 first value is in cell J2 and goes to W2. There are a few zero's in between but I'd like this range to be CF. I'm basically looking for a field goal CF of the values, excluding the zero's outside of those field goal posts.

The data is getting fed from another worksheet. Each of the numbers represented is a INDEX/MATCH formula. If there any blanks on the master data sheet, it always produces a zero here. If I attempt to use this formula as the CF:

Please Login or Register  to view this content.

everything CF's. However, if I were to physically delete the formulas from cells B2:I2 and X2:Z2, the formula would work correctly. Unfortunately, I can't just delete those cells because the data will always change.

I've also attempted to mirror the table just below with a

Please Login or Register  to view this content.

and apply the CF to the new table but I still get the entire row CF when I really only want the first value to last value to CF.

Any ideas or suggestions?

[purdue7997]

I figured it out, sorta. I just used the mirror table mentioned above and changed my COUNTA to a COUNT. This gave me what I want, albeit a mirrored table.