How to get excel to ignore criteria if cell is blank? (Min/Max/Average/Countif)

**nat.ssnt** · 10-15-2013, 07:54 AM

Hi,

I am excel 2003 user

I have multiple criteria to locate information e.g. Price history within my database.
(Criteria: e.g. Industry, Period, credit rating)

One of the criteria is sometimes blank and if so, I would like for excel to ignore this criteria altogether and locate the information based on the remaining information, instead of trying to match the blank cell to the result.

The functions are Min/Max/ Average price info within database.

This is currently the formula that I have managed to build, which will include the blank cell to locate the price info.

=MIN(IF((Database!$C$2:$C$14>=$B$4)*(Database!$C$2:$C$14<='Output - Summary'!$C$4)*(Database!$B$2:$B$14='Output - Summary'!$B$5),Database!P2:$P$14,""))

Your help/advice will be much appreciated!

Cheers,
Nat

**:) Sixthsense :)** · 10-16-2013, 02:20 AM

Please attach a sample workbook with enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.).

**nat.ssnt** · 10-16-2013, 11:41 AM

Hi! Thanks for the quick reply! I actually managed to find the answer to this

My initial issue was, I wanted to locate historical price data, based on 3 criteria, e.g. Country, Period, Credit Rating. At some point of time, I wanted to leave one of the criteria "blank", and locate the price based only on 2 or even 1 criteria.

I followed this formula

=MIN(IF(((D13:D19=B5)+(B5=""))*((F13:F19=B4)+(B4=""))*((G13:G19=B6)+(B6="")),A13:A19,""))

**arlu1201** · 10-17-2013, 06:22 AM

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 -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

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.