Formula gives out wrong result, Help Please

This is my first time writing out formulas for excel and I'm only drawing on basic knowledge and some knowledge from computer programming classes in college to write these formulas (5+ years ago). I don't know excel terms or how some stuff works (dont even know what VBA means or is), so please understand I am a complete newbie at this. I may use the wrong terminology or word this incorrectly, so please be patient with me. Please help me figure out whats causing my formula to give out the wrong data. Thank you in advance

So what I'm trying to do is M16 is an array to comb through the data and find the cells that passes all the criterias/rules(if and or functions), then show the min amongst the cells within that array. If no cells pass the criterias/rules, I want it to show 0.

Problem: **Please check my sheet for reference** Based on my formula, all the imported data doesn't have an entry/cell that meets the criteria of the formula, yet it still gives out a number (D11 "560,000"). Row 11 passes most of the formula but doesn't satisfy the H criterias/rules (H11 "2.00 (2 0 0 0)") yet manages to still be displayed. Why is it showing D11? Did I use the quotation marks incorrectly? How do I have it show 0 since no cells satisfy my formula? Any help would be greatly appreciated!

Here is a copy:
{=MIN(IF((($B:$B="C/C")*($G:$G=2)*OR($H:$H="3.00 (3 0 0 0)",$H:$H="3.00 (2 1 0 0)",$H:$H="3.00 (1 2 0 0)",$H:$H="3.00 (0 3 0 0)")*IF(DATE(YEAR($I:$I),MONTH($I:$I)+6,DAY($I:$I))>TODAY(),TRUE,0)),$D:$D))}

Hi,

Try below formula in M16:

Formula:

Please Login or Register  to view this content.

Just for suggestion, never used full column or row ref. in formulas. If you are not sure about the last row of you data is going to be, you can always used dynamic named range in the formula. This formula is slowing your spreadsheet. If the formula is giving correct result than write back I will guide you how to define dynamic named range.

I think it can be further short down to as below:

=MIN(IF(($B2:$B37="C/C")*($G2:$G37=2)*(($H2:$H37="3.00 (3 0 0 0)")+($H2:$H37="3.00 (2 1 0 0)")+($H2:$H37="3.00 (1 2 0 0)")+($H2:$H37="3.00 (0 3 0 0)"))*($I2:$I37>TODAY()),$D2:$D37))

Confirm With Ctrl+Shift+Enter.

@speshul. Thanks for pointing in right direction.

Thank you 3 for helping me out. I used the formula you guys suggested and it works perfectly! As misrasomendra and speshul suggested, I took out the whole column range since it was lagging up the whole system; I did a range from B2:B100 since I doubt data will exceed that amount.

Thanks benishiryo for those suggestions. My incoming data varies in size so im not sure if offset and index would work? I'm not very familiar with those functions so I might not fully understand their potential.

Besides all that, I got it to work and still working on these spreadsheets. Thank you so much for all the generous help.