Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display leading zeros in numeric value without converting to text | Excel Discussion (Misc queries) | |||
Leading zeroes get dropped when converted to text | Excel Discussion (Misc queries) | |||
Remove text leading zero in text string | Excel Worksheet Functions | |||
Remove text leading zero in text string | Excel Worksheet Functions | |||
save text field w/ leading zeros in .csv format & not lose zeros? | Excel Discussion (Misc queries) |