Countifs help needed

[GarryFreeman]

Hoping someone can help me here.
You will see that I cannot save this as a mini sheet as my Excel hates the add in and refuses to allow me to unblock the macros...

I was helped with another forum member earlier in the year with the below formula. At the time it was perfect for what I needed, but now trying to use the same formula for another piece of work.
The challenge comes when one of the figures on row 10, column K -P is Zero. in the below example I would want cell I10 to return a value of 2591 (jan 22+ Feb22+Mar22) but the zero in cell L10 is creating havoc with the formula and only adding Jan + Feb
I could add in 0.000001 but the challenge is the data is hundreds of lines long.

Any help as always would be greatly appreciated

Thanks

Garry

 

[Legacy 143009]

I didn't understand your formula. How about this:

=SUM(OFFSET(B10,0,0,1,MATCH(2,1/(K10:P10>0))))

 

[GarryFreeman]

I didn't understand your formula. How about this:

=SUM(OFFSET(B10,0,0,1,MATCH(2,1/(K10:P10>0))))

This did not seem to work. What I am trying to calculate is the year to date total based on the month next year.
In the example below if there was data in for April then the formula should total Jan - April from the left hand table. But the zero in column L (sum total of C2:C4) is hampering that.

Thanks

Garry

 

[Fluff]

How about

=SUM(TAKE(B10:G10,,MAX(FILTER(SEQUENCE(,6),K10:P10>0))))

 

[Legacy 143009]

Weird. I have tested it. What is the result for my formula?

 

[GarryFreeman]

How about

=SUM(TAKE(B10:G10,,MAX(FILTER(SEQUENCE(,6),K10:P10>0))))

This gives me the below error Fluff

 

[GarryFreeman]

Weird. I have tested it. What is the result for my formula?

it just returns #value ?

 

[Legacy 143009]

Ahh sorry I see. Ok it is an array formula. You should press Ctrl+Shift+Enter together after paste.

I saw you are using XL365

 

[GarryFreeman]

Ahh sorry I see. Ok it is an array formula. You should press Ctrl+Shift+Enter together after paste.

I saw you are using XL365

BINGO Thanks for that. Much appreciated

 

[Fluff]

What version of Excel are you using?