SUMIF Criteria

[bbrd]

Is there a character limitation on the criteria value on a sumif?
The criteria I am using is 20 characters long and is returning invalid
values.


Thanks

 

[Pete_UK]

Perhaps you have both upper and lower case letters that you think are
not the same but SUMIF (and COUNTIF) do not make this distinction.

Hope this helps,

Pete

 

[bbrd]

My function looks like this

sumif(range1,"12345678901234567890",range2) .

I trimmed the criteria to 16 characters and it pulled the correct
data. With 20 characters it summed all values that had the same first
15 characters in the criteria.

Neil

 

[Pete_UK]

Excel only has 15-digit accuracy in numbers - any numbers larger than
this have the lower-order numbers truncated to zeros, so you will get
matches in formulae where you might not expect them.

Hope this helps.

Pete

 

[Harlan Grove]

My function looks like this

sumif(range1,"12345678901234567890",range2) .

I trimmed the criteria to 16 characters and it pulled the correct
data. With 20 characters it summed all values that had the same first
15 characters in the criteria.

If your range1 values are text, use

=SUMPRODUCT(--(range1="12345678901234567890"),range2)

If your range1 values are numeric, they're not stored with 20 decimal
digits precision, only 15. SUMIF criteria that looks numeric is
interpretted as numeric, so your formula above compares truncated or
rounded numbers in range1 to 123456789012346*10^5. If you meant for
your range1 to hold distinct numeric values that may have differed
only in the last 5 decimal digits, your data is corrupted.