SUMIF, Text, and Leading, 000s
 

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.


--
Stephen

SUMIF, Text, and Leading, 000s
 

Stephen wrote...
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)

SUMIF, Text, and Leading, 000s
 

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:B1 0)

HTH
Kostis Vezerides

On Jan 29, 8:51 pm, Stephen wrote:
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.

--
Stephen

SUMIF, Text, and Leading, 000s
 

Thanks all for responding.

Both of these solutions solve my problem.


--
Stephen


"vezerid" wrote:

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:B1 0)

HTH
Kostis Vezerides

On Jan 29, 8:51 pm, Stephen wrote:
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.

--
Stephen