# Find minimum (ignoring blank cell or zero values) with Multiple Criteria

1. ## Find minimum (ignoring blank cell or zero values) with Multiple Criteria

Hi,

I have managed to get the array to work using

{=MIN(IF((A2:A12=E2)*(B2:B12=F2),C2:C12))}

However, it is taking the "blank cells" as "zero" as the minimum values. How do I write it so that it will ignore the blank cell? And how to write it if trying to get it to ignore "zero" values?

If someone can help me... I really appreciate it.

Joey

2. ## Re: Find minimum (ignoring blank cell or zero values) with Multiple Criteria

try it like this

Formula:
`Please Login or Register  to view this content.`

3. ## Re: Find minimum (ignoring blank cell or zero values) with Multiple Criteria

or
Formula:
`Please Login or Register  to view this content.`

4. ## Re: Find minimum (ignoring blank cell or zero values) with Multiple Criteria

Ah... I see how you included the Nested IF to ignore blank cells. Thank you vlady!

Following your 'Nested IF' idea, I've further changed the equation to also ignore "zero values". I thought I should post the formula here for future reference if anyone else looking for such answer.

Formula:
`Please Login or Register  to view this content.`

5. ## Re: Find minimum (ignoring blank cell or zero values) with Multiple Criteria

If you want to ignore blanks and zeroes then a single test for that is sufficient, i.e.

=MIN(IF((A\$2:A\$12=E2)*(B\$2:B\$12=F2)*(C\$2:C\$12<>0),C\$2:C\$12))

6. ## Re: Find minimum (ignoring blank cell or zero values) with Multiple Criteria

Thank you all for quick response.

How do I get the equation to return a "No Data" instead of "zero" when Column C has only blank for the selected criteria?

Thanks again!

Thanks zbor

8. ## Re: Find minimum (ignoring blank cell or zero values) with Multiple Criteria

with any of the formula's given above you can include an if statement

=if(theformula=0,"No Data",theformula)

note: take the shortest formula by DDL.

regards,

9. ## Re: Find minimum (ignoring blank cell or zero values) with Multiple Criteria

:D... Thanks so much vladimir - Works perfectly!

Cheers
Joey

10. ## Re: Find minimum (ignoring blank cell or zero values) with Multiple Criteria

MIN always returns zero if no criteria are met, if you switch to SMALL you can get #NUM! error instead, i.e.

=SMALL(IF((A\$2:A\$12=E2)*(B\$2:B\$12=F2)*(C\$2:C\$12<>0),C\$2:C\$12),1)

....and take that one step further by adding IFERROR to convert to "No data", i.e.

=IFERROR(SMALL(IF((A\$2:A\$12=E2)*(B\$2:B\$12=F2)*(C\$2:C\$12<>0),C\$2:C\$12),1),"No data")

11. ## Re: Find minimum (ignoring blank cell or zero values) with Multiple Criteria

Ah... I have not known about SMALL and the IFERROR commands. This is exactly what I need to run my calculations! Thank you daddylonglegs

12. ## Re: Find minimum (ignoring blank cell or zero values) with Multiple Criteria

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -

Incase your issue is not solved, you can undo it as follows -

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

13. ## Re: Find minimum (ignoring blank cell or zero values) with Multiple Criteria

Thank you arlu. I'm learning how to navigate this site!

There are currently 1 users browsing this thread. (0 members and 1 guests)