 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / September 2007SUMIF and text with decimals
Why does Excel’s SUMIF formula consider cells with text data such as: 2.1 and 2.10 as equal (it sums their values) when used in the range and criteria? SUMIF (and COUNTIF) does not make a difference between numbers and numbers represented as text, you would need to use SUMPRODUCT for that =SUMPRODUCT(--(A2:A100="2.10"),B2:B100) will sum B2:B100 where A2:A100 equals "2.10"  SignatureRegards, Peo Sjoblom > Why does Excel's SUMIF formula consider cells with text data such as: > 2.1 and 2.10 as equal (it sums their values) when used in the range and > criteria? Thank you - that works > SUMIF (and COUNTIF) does not make a difference between numbers and numbers > represented as text, you would need to use SUMPRODUCT for that [quoted text clipped - 6 lines] > > 2.1 and 2.10 as equal (it sums their values) when used in the range and > > criteria? Nel post:AB1266D9-04AC-4738-9130-20D649B02727@microsoft.com, Mark <Mark@discussions.microsoft.com> ha scritto: > Why does Excel’s SUMIF formula consider cells with text data such as: > 2.1 and 2.10 as equal (it sums their values) when used in the range > and criteria? Hi Mark, because Excel treat as number text that can be treated as number, when you use it in calculation. So if you use a number formatted as text in a calculation, it will be treated as a number. Signature(I'm not sure of names of menus, options and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy Only some function do that, not SUMPRODUCT for instance SignatureRegards, Peo Sjoblom > Nel post:AB1266D9-04AC-4738-9130-20D649B02727@microsoft.com, > Mark <Mark@discussions.microsoft.com> ha scritto: [quoted text clipped - 9 lines] > So if you use a number formatted as text in a calculation, it will be > treated as a number. Thank you for clarification Signature(I'm not sure of names of menus, options and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy > Only some function do that, not SUMPRODUCT for instance > [quoted text clipped - 11 lines] >> So if you use a number formatted as text in a calculation, it will be >> treated as a number. Thanks Franz > Nel post:AB1266D9-04AC-4738-9130-20D649B02727@microsoft.com, > Mark <Mark@discussions.microsoft.com> ha scritto: [quoted text clipped - 9 lines] > So if you use a number formatted as text in a calculation, it will be > treated as a number. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.