SUMIF, Text, and Leading, 000s

[Guest]

I am having difficulty with using SUMIF over a range of text formatted
numbers. When my criteria is 0001234 it sums the corresponding values
associated with 01234, 1234 and 001234. Is there a way to further restrict
the summed values for only those item exactly matching 0001234?

Thanks for any help you may be able to provide to me.

 

[Harlan Grove]

I am having difficulty with using SUMIF over a range of text formatted
numbers. When my criteria is 0001234 it sums the corresponding values
associated with 01234, 1234 and 001234. Is there a way to further restrict
the summed values for only those item exactly matching 0001234?

Yes, the criteria expressions used by SUMIF and COUNTIF are quite
crude. If the criteria expression looks like a number, Excel treats it
as a numeric comparison. The only way to force Excel to make it a text
comparison is to use two function calls using different wildcards
added to the criteria expression. In this case,

=SUMIF(rngA,"0001234*",rngB)-SUMIF(rngA,"0001234?*",rngB)

Alternatively, use SUMPRODUCT.

=SUMPRODUCT(--(rngA="0001234"),rngB)

 

[vezerid]

Stephen,
SUMIF will do this. You need to try a more "requiring" formula. I
assume that your cell containing 001234 is formatted as Text. Say
A1:A10 are the cells holding the 0123 etc. and B1:B10 are the cells
from which you want the subtotal.

=SUMPRODUCT(ISTEXT(A1:A10)*(A1:A10="001234")*B1:B10)

HTH
Kostis Vezerides

 

[Guest]

Thanks all for responding.

Both of these solutions solve my problem.