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

 

[Fluff]

This is another array formula, but is no-volatile

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

 

[Legacy 143009]

This is another array formula, but is no-volatile

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

I also thought of this formula but then I changed my mind because I can't understand how it works.
When you evaluate the INDEX part of the formula it should return 841 (only thinking).
Howcome is SUM(B10:841) a meaningful function?

 

[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

What version of Excel are you using?

Office pro 2019 and also tried on 365 enterprise but this gave a #NAME? result

 

[Fluff]

Office pro 2019

In that case I suggest you change your profile to show that.
The #NAME error probably means that you don't have the TAKE function.

 

[GarryFreeman]

Thanks to everyone for your suggestions. You guys are the best

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Fluff]

When you evaluate the INDEX part of the formula it should return 841 (only thinking).

In cases like this Index will return the cell address so you get Sum(B10:D10)

 

[Legacy 143009]