Why does Excels 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




"Mark" wrote in message
...
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?

Nel ,
Mark ha scritto:
Why does Excels 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

Thank you - that works

"Peo Sjoblom" wrote:

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




"Mark" wrote in message
...
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?

Only some function do that, not SUMPRODUCT for instance


--


Regards,


Peo Sjoblom




"Franz Verga" wrote in message
...
Nel ,
Mark 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.


--
(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 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
"Peo Sjoblom" ha scritto nel messaggio
...
Only some function do that, not SUMPRODUCT for instance


--


Regards,


Peo Sjoblom




"Franz Verga" wrote in message
...
Nel ,
Mark 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.


--
(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" wrote:

Nel ,
Mark ha scritto:
Why does Excels 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