Sumproduct Formula Not Working Consistently - Date Issue

**gav0101** · 09-18-2008, 11:48 AM

Sorry about that. I didn't realize the attachment was too big and didn't attach. I stripped off a bunch of the data so it would fit.

**NBVC** · 09-18-2008, 12:09 PM

It has to do with the precision in Excel's calculation... the formula result you have is not exactly the same as the date translation Excel uses.

Try this, in C5, to get better accuracy:

=SUMPRODUCT--(ROUND(Data!$A$2:$A$1326,6)=ROUND($A5,6)),--(Data!$D$2:$D$1326=C$4),(Data!$N$2:$N$1326))

copied down and across the table.

**gav0101** · 09-18-2008, 03:57 PM

Thanks NBVC. I kind of figured it had something to do with the formula and the seconds.

Is the ",6" in the round function saying round the number to the sixth position in this case seconds. If yes, I would think I should round to the 4th position in this case minutes, because I won't have any seconds in the data.