Min IF Errors

[swjones540]

I was wondering if you could help.

I am trying to use Min If to return the lowest date that we invoiced a client. we have thousands of rows of invoices and i am using the IF function to match the client name and site before it returns the earliest date.

The formula i am using is : {=MIN(IF(C$2:C$7057=P2,IF(D$2:D$7057=Q2,F$2:F$7057,)))}

It works for some rows but not all, but the Max IF works for a lot more.

Any help would be greatly appreciated, im pretty stumped.

 

[steve the fish]

Its almost certainly not to do with the formula but that the matches are not matching. Formula looks ok to me. Check that C2 for example does definitely equal P2 and the same with D & Q. Also make sure all of your numbers are true numbers.

 

[Momentman]

There seems to be an extra comma at the end of the formula

Modify to =MIN(IF(C$2:C$7057=P2,IF(D$2:D$7057=Q2,F$2:F$7057))) and press ctrl+shift+enter to enter as an array formula

 

[swjones540]

Thank you momentman, hero.. cant believe it was just a comma

 

[steve the fish]

Now im stumped. What difference did the comma make?

 

[barry houdini]

When you use this version

=MIN(IF(C$2:C$7057=P2,IF(D$2:D$7057=Q2,F$2:F$7057,)))

The part after the comma is what the formula returns if the second IF condition is FALSE, if you put in the comma but then nothing after it's the equivalent of putting a zero there, so, depending on the values, you may often (incorrectly) get zero as the MIN.

If you leave out the comma then the FALSE result is just FALSE.....and the MIN function will ignore that

 

[Momentman]

as far i know, The extra comma would mean that you have put a value of zero if the expression IF(D$2:D$7057=Q2 were to be false. Taking out the comma ignores all false values and returns the values in F$2:F$7057 when the expression is true. something like that

See how comma makes a difference in the values returned by 2 different formulas

Excel 2012
	A	B	C
1	8	0	FALSE
Sheet4

Cell Formulas
Range	Formula
B1	=IF(A1<6,"good",)
C1	=IF(A1<6,"good")

 

[steve the fish]

Ah I get it now. So it only errors where it sees a true result in C2:C7057=P3 and there is a result where D2:D7057 doesn't equal Q3? Then assuming no true results are negative the result is 0?