• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Average Between Dates & Exclude 0

MyCon

Member
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
 

Debraj

Excel Ninja
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..
 
Top