# 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")
``````
Book1.xlsx
LVL 2
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EngineerCommented:
Try

=MIN(IF((C1:C14<>0)*(A1:A14="apples"),A1:A14))
0
Author Commented:
Still returns a zero (using the attached sample worksheet).
0
EngineerCommented:
Sorry change it to

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

Experts Exchange Solution brought to you by