How to Average Only Non-Zero Values Using AverageIfs with Index/Match

[DrExcellence]

Here is my current formula:

AVERAGEIFS ( INDEX ( $H$10:$X$505, , MATCH ( [SKU], $H$10:$X$10, 0)), $E$10:$E$505, [City], $F$10:$F$505, [Store Name])

I am trying to summarize the average prices (H11-X505) by product (header cells H10-X10), city and store (Columns E-F) in a separate table using this formula. It works fine, but the problem I'm facing is that the average is including zeroes and incorrectly giving me very low numbers. Does anyone know what I need to add to this formula so that it averages only non-zero values? (I'm not able to change the zero values to blanks because the zeroes are necessary for other functionality).

Any guidance will be greatly appreciated.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=LET(Rng,INDEX($H$10:$X$505, ,MATCH([SKU], $H$10:$X$10, 0)),AVERAGEIFS(Rng,Rng,"<>0", $E$10:$E$505, [City], $F$10:$F$505, [Store Name]))

 

[DrExcellence]

Hi & welcome to MrExcel.
How about

=LET(Rng,INDEX($H$10:$X$505, ,MATCH([SKU], $H$10:$X$10, 0)),AVERAGEIFS(Rng,Rng,"<>0", $E$10:$E$505, [City], $F$10:$F$505, [Store Name]))

Thanks so much!! Works perfectly!

 

[Fluff]

You're welcome & thanks for the feedback.