SUMIF and text with decimals

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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
 
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
 
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...
 
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
 
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...
 
Back
Top