COUNTIF SUMIF Limitations

I'm sure many of you knew this but it was new to me and I came across it today, so in an effort to get it out there a bit more, i document it here.

It concerns the fact that COUNTIF and SUMIF (and their 2007 plural counterparts SUMIFS and COUNTIFS) carry forward the limitation that Excel has of 15 significant digits. (As does 1-2-3 and OpenOffice)

http://support.microsoft.com/kb/269370

I knew this limitation, but didn't realise the two functions concerned followed this limit'

I had to build an automated reconciliation model to delete off matching records. It operates through several iterations but my first one was to concatenate Date, Customer Number and Credit/Debit amount and then do a COUNTIF on that 'Key'. If this resulted in 2, then I could presume a  'match' and delete the record.

Imagine this then. I have data as the screenshot below

In D3 is the formula =A3&B3&C3
In E3 (as you can see) is the formula =COUNTIF(D:D,D3)
In F3 is effectively the same formula as in E3 but using the 2007 function COUNTIFS() (using just one criteria)
In G3 is the formula =SUMIF(D:D,D3,C:C)
In H3 is the formula =SUMIFS(C:C,D:D,D3) (Notice how in this 2007 version the parameter order is changed)
In I3 is the formula =SUMPRODUCT(--($D$3:$D$4=D3))
In J3 is the formula =SUMPRODUCT(($D$3:$D$4=D3)*($C$3:$C$4))

What COUNTIF and SUMIF (and their plural Excel 2007 equivalents) are seeing is a string in both cells (D3 and D4) that are the same as they are just taking the first 15 digits (200806101234561). Plainly, in my scenario this is incorrect. SUMPRODUCT however sees all the digits and therefore overcomes this limitation. Correctly seeing a COUNT of 1 each and a SUM of just the single values.

For a complete explanation of SUMPRODUCT you could do worse than look here

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

BTW: As a sideline, the formula using SUMPRODUCT worked about twice as fast