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?

[Webinar] Streamline your web hosting managementRegister Today

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
All Courses

From novice to tech pro — start learning today.