Two formula product different result 2% of time. WHy?

**BRISBANEBOB** · 08-23-2013, 11:06 PM

I am using two different formula (as a form of check) to extract data from an array of 7000 lines. In 98% of the results the correlate. But why not 100%

=IF(ROUNDDOWN(SUMPRODUCT((--(Data!$A$6:$A$10000=Date!G6749)*(Data!$G$6:$G$10000=Date!F6749))*(--(Data!$M$6:$M$10000))),0)=0,"",ROUNDDOWN(SUMPRODUCT((--(Data!$A$6:$A$10000=Date!G6749))*(--(Data!$G$6:$G$10000=Date!F6749))*(Data!$M$6:$M$10000)),0))

=IF(ISNA(INDEX(Data!M$6:M$15000,MATCH(Date!$A6749,Date!N$3:N$14997,0))),"",ROUNDDOWN(INDEX(Data!M$6:M$15000,MATCH(Date!$A6749,Date!N$3:N$14997,0)),0))

The result being returned is a date and the incorrect result being returned is 'double' the correct result. Weird. Double weird...

Grateful for anyone who can point out where I am going wrong.

Unfortunately the data is way to big to include and due to the random distribution of criteria it has proved almost impossible to edit down. If commercial services can accept 2 Mb files please let me know.

**RobertMika** · 08-24-2013, 12:40 AM

Looking briefly at your formulas:
SUMPROUCT+ROUNDDOWN is returning SUM of your values
ROUNDOWN+INDEX is returning only the first values.
So if you got more that one correct values for your criteria in first formula that's why you have "double" correct result.
Use Evaluate Formula tool(on smaller ranges) to see where the "problems" occures.