Average Between Dates & Exclude 0

[MyCon]

Hi,

Seeking a function that will average between dates & exclude 0s

I have the following that works well individually & now seeking to either combine or get another variation:

=AVERAGEIFS($I$11:$I$486,$B$11:$B$486,">="&M6,$B$11:$B$486,"<="&N6)

Cell M6 = 12/01/14
Cell N6 = 12/31/14


=SUM(I11:I84)/(SUMPRODUCT((I11:I84<>0)*1))

How can I combine these or open to other variations that will get desired results.

Thanks

 

[Somendra Misra]

Hi MyCon,

Care to upload a sample file also and to explain your intention.

Regards,

 

[Debraj]

Its AverageIfS
You can add many more validation in the same..
=AVERAGEIFS($I$11:$I$486,$B$11:$B$486,">="&M6,$B$11:$B$486,"<="&N6,$I$11:$I$486,">0")

and regarding Processing speed.. much faster than SUMPRODUCT.. and long range..

 

[MyCon]

Hi Debraj,

Your example works great!! Exactly what I was seeking.

Thanks