SUMIF and text with decimals

[Guest]

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?

 

[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

 

[Franz Verga]

Nel post:[email protected],

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.


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

 

[Guest]

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

=SUMPRODUCT(--(A2:A100="2.10"),B2:B100)

will sum B2:B100 where A2:A100 equals "2.10"




--


Regards,


Peo Sjoblom

 

[Peo Sjoblom]

Only some function do that, not SUMPRODUCT for instance


--


Regards,


Peo Sjoblom

 

[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

 

[Guest]

Thanks Franz

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