Find MIN, MAX and AVERAGE values in an array based on criteria and excluding zeros

In the screeshot below, I want to find the minimum value for 'apples' but ignore zeros.  In this case, the result should be 4.

Also need the max and average values for the same criteria.

Attempted to use this array formula but it returns zero.

=MIN(IF(C1:C14<>0,A1:A14))*(A1:A14="apples")

Open in new window

screenshotBook1.xlsx
LVL 2
mcnuttlawAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Sorry change it to

=MIN(IF((C1:C14<>0)*(A1:A14="apples"),C1:C14))
0
 
Saqib Husain, SyedEngineerCommented:
Try

=MIN(IF((C1:C14<>0)*(A1:A14="apples"),A1:A14))
0
 
mcnuttlawAuthor Commented:
Still returns a zero (using the attached sample worksheet).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.