Sum.if()

[Henning]

I am having problems vidth the sum.if() function.

my search key is concatenated text from Debitorno.(8-12char)+month(2
char)+year(2 char)+itemno.(6-8 char) but it seems like sum.if() only looks
af the first 16 caracters and ignores the rest.

Anyone else who experienced that? any suggestions for a workaround?

TIA
Henning

 

[Andy B]

Hi

As far as I know SUMIF() has no limitations like that. Post your formula for
us to see.

 

[Ron Rosenfeld]

I am having problems vidth the sum.if() function.

my search key is concatenated text from Debitorno.(8-12char)+month(2
char)+year(2 char)+itemno.(6-8 char) but it seems like sum.if() only looks
af the first 16 caracters and ignores the rest.

Anyone else who experienced that? any suggestions for a workaround?

TIA
Henning

SUMIF does not have that limitation. However, if in the process of setting up
your search key, you have transformed it into a number, and if the 16th
character is a zero, then you may see the behavior you are describing as Excel
precision is limited to fifteen digits.


--ron

 

[Henning]

YES!

I could not thange the key cell to type text, because if I did, my
concatenation-formula would not work.

- but when I add an "x" to all keys to prevent them from converting to
numbers - it seems to work.

Thanks!

Henning

 

[Henning]

It is difficult to make at simple example bu please look at my answer to
Ron, I think he gave me the clou...

regards
Henning

 

[Arvi Laanemets]

Hi

When to generate the search key you used the formula like
=Debitorno & MonthNo & YearNo & Itemno
with all components as numbers or numeric strings (i.e. there is no
non-numeric character in result), then maybe the Excel can sometimes
interpret the result as number. (I'm not sure about it, but it's a
possibility to consider)

 

[Ron Rosenfeld]

YES!

I could not thange the key cell to type text, because if I did, my
concatenation-formula would not work.

- but when I add an "x" to all keys to prevent them from converting to
numbers - it seems to work.

Thanks!

You're welcome.

But I don't think you need to add 'x' to the keys.

If you concatenate by using either the concatenate worksheet function, or the
'&' operator, they turn numbers into strings.

What sort of formula are you using to concatenate?


--ron