SUMIF and text with decimals

M

Mark

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?
 
P

Peo Sjoblom

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"




--


Regards,


Peo Sjoblom
 
F

Franz Verga

Nel post:[email protected],
Mark said:
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?
Click to expand...

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.


--
(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
 
M

Mark

Thank you - that works

Peo Sjoblom said:
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"




--


Regards,


Peo Sjoblom
Click to expand...
 
P

Peo Sjoblom

Only some function do that, not SUMPRODUCT for instance


--


Regards,


Peo Sjoblom
 
F

Franz Verga

Thank you for clarification

--
(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
 
M

Mark

Thanks Franz

Franz Verga said:
Nel post:[email protected],


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.


--
(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
Click to expand...
 
Top