Using Windows XP and Office2003:

In column AJ we have a formula
....
=CONCATENATE(A97;G97;H97)
=CONCATENATE(A98;G98;H98)
....

the result of this is either 14 or 15 characters (depending on the month of
the year)

in column AT we do a COUNTIF:
....
=COUNTIF($AJ$83:$AJ$4916;AJ97)
=COUNTIF($AJ$83:$AJ$4916;AJ98)

since all of the values in the AJ column are unique, all of these formulas
should have "1" as a result, however, the ones with a string in the AJ column
of 15 characters give "3" as a result (conclusion: that's for months 10, 11
and 12 where the 0, 1 and 2 aren't recognized/counted).

This would mean that only the first 14 characters are evaluated. I've tried
several workarounds, but in itself these functions should work (or am
I missing an elephant here).

I've also seen questions about formulas that only work when they refer to
cells with straight numbers instead of formulas, but even when I replace the
'concatenate' results with the resulting value of the cell, the result stays
the same.

For another formula on another sheet to work we need to have this error
sorted out, and so far I haven't found what causes this. Can you point me in
the right direction?

=CONCATENATE(A97;G97;H97)
=CONCATENATE(A98;G98;H98)
the result of this is either 14 or 15 characters
(depending on the month of the year)

Sounds like you're "building" date strings.

Post some examples of the resulting strings from the above formulas.

--
Biff
Microsoft Excel MVP


"JusMe" wrote in message
...
Using Windows XP and Office2003:

In column AJ we have a formula
...
=CONCATENATE(A97;G97;H97)
=CONCATENATE(A98;G98;H98)
...

the result of this is either 14 or 15 characters (depending on the month
of
the year)

in column AT we do a COUNTIF:
...
=COUNTIF($AJ$83:$AJ$4916;AJ97)
=COUNTIF($AJ$83:$AJ$4916;AJ98)

since all of the values in the AJ column are unique, all of these formulas
should have "1" as a result, however, the ones with a string in the AJ
column
of 15 characters give "3" as a result (conclusion: that's for months 10,
11
and 12 where the 0, 1 and 2 aren't recognized/counted).

This would mean that only the first 14 characters are evaluated. I've
tried
several workarounds, but in itself these functions should work (or am
I missing an elephant here).

I've also seen questions about formulas that only work when they refer to
cells with straight numbers instead of formulas, but even when I replace
the
'concatenate' results with the resulting value of the cell, the result
stays
the same.

For another formula on another sheet to work we need to have this error
sorted out, and so far I haven't found what causes this. Can you point me
in
the right direction?

the result is a string of numbers .... like these:

50000356020079
500003560200710

the 'countif' one works with the first one, and doesn't seem to work with
the second one



"T. Valko" wrote:

=CONCATENATE(A97;G97;H97)
=CONCATENATE(A98;G98;H98)
the result of this is either 14 or 15 characters
(depending on the month of the year)

Sounds like you're "building" date strings.

Post some examples of the resulting strings from the above formulas.

--
Biff
Microsoft Excel MVP


"JusMe" wrote in message
...
Using Windows XP and Office2003:

In column AJ we have a formula
...
=CONCATENATE(A97;G97;H97)
=CONCATENATE(A98;G98;H98)
...

the result of this is either 14 or 15 characters (depending on the month
of
the year)

in column AT we do a COUNTIF:
...
=COUNTIF($AJ$83:$AJ$4916;AJ97)
=COUNTIF($AJ$83:$AJ$4916;AJ98)

since all of the values in the AJ column are unique, all of these formulas
should have "1" as a result, however, the ones with a string in the AJ
column
of 15 characters give "3" as a result (conclusion: that's for months 10,
11
and 12 where the 0, 1 and 2 aren't recognized/counted).

This would mean that only the first 14 characters are evaluated. I've
tried
several workarounds, but in itself these functions should work (or am
I missing an elephant here).

I've also seen questions about formulas that only work when they refer to
cells with straight numbers instead of formulas, but even when I replace
the
'concatenate' results with the resulting value of the cell, the result
stays
the same.

For another formula on another sheet to work we need to have this error
sorted out, and so far I haven't found what causes this. Can you point me
in
the right direction?

Could it be since your strings are all numbers, that there are just too many
'significant' numbers for xl to deal with?
--
John C


"JusMe" wrote:

the result is a string of numbers .... like these:

50000356020079
500003560200710

the 'countif' one works with the first one, and doesn't seem to work with
the second one



"T. Valko" wrote:

=CONCATENATE(A97;G97;H97)
=CONCATENATE(A98;G98;H98)
the result of this is either 14 or 15 characters
(depending on the month of the year)

Sounds like you're "building" date strings.

Post some examples of the resulting strings from the above formulas.

--
Biff
Microsoft Excel MVP


"JusMe" wrote in message
...
Using Windows XP and Office2003:

In column AJ we have a formula
...
=CONCATENATE(A97;G97;H97)
=CONCATENATE(A98;G98;H98)
...

the result of this is either 14 or 15 characters (depending on the month
of
the year)

in column AT we do a COUNTIF:
...
=COUNTIF($AJ$83:$AJ$4916;AJ97)
=COUNTIF($AJ$83:$AJ$4916;AJ98)

since all of the values in the AJ column are unique, all of these formulas
should have "1" as a result, however, the ones with a string in the AJ
column
of 15 characters give "3" as a result (conclusion: that's for months 10,
11
and 12 where the 0, 1 and 2 aren't recognized/counted).

This would mean that only the first 14 characters are evaluated. I've
tried
several workarounds, but in itself these functions should work (or am
I missing an elephant here).

I've also seen questions about formulas that only work when they refer to
cells with straight numbers instead of formulas, but even when I replace
the
'concatenate' results with the resulting value of the cell, the result
stays
the same.

For another formula on another sheet to work we need to have this error
sorted out, and so far I haven't found what causes this. Can you point me
in
the right direction?

Try using SUMPRODUCT.

CONCATENATE returns a text value *but* COUNTIF evalauates text numbers and
numeric numbers as being equal. That's probably why it's truncating the last
digit since Excel will only evaluate to 15 significant digits. You will
probably have to format your range $AJ$83:$AJ$4916 as TEXT if it isn't
already.

=SUMPRODUCT(--($AJ$83:$AJ$4916=AJ97))

--
Biff
Microsoft Excel MVP


"John C" <johnc@stateofdenial wrote in message
...
Could it be since your strings are all numbers, that there are just too
many
'significant' numbers for xl to deal with?
--
John C


"JusMe" wrote:

the result is a string of numbers .... like these:

50000356020079
500003560200710

the 'countif' one works with the first one, and doesn't seem to work with
the second one



"T. Valko" wrote:

=CONCATENATE(A97;G97;H97)
=CONCATENATE(A98;G98;H98)
the result of this is either 14 or 15 characters
(depending on the month of the year)

Sounds like you're "building" date strings.

Post some examples of the resulting strings from the above formulas.

--
Biff
Microsoft Excel MVP


"JusMe" wrote in message
...
Using Windows XP and Office2003:

In column AJ we have a formula
...
=CONCATENATE(A97;G97;H97)
=CONCATENATE(A98;G98;H98)
...

the result of this is either 14 or 15 characters (depending on the
month
of
the year)

in column AT we do a COUNTIF:
...
=COUNTIF($AJ$83:$AJ$4916;AJ97)
=COUNTIF($AJ$83:$AJ$4916;AJ98)

since all of the values in the AJ column are unique, all of these
formulas
should have "1" as a result, however, the ones with a string in the
AJ
column
of 15 characters give "3" as a result (conclusion: that's for months
10,
11
and 12 where the 0, 1 and 2 aren't recognized/counted).

This would mean that only the first 14 characters are evaluated. I've
tried
several workarounds, but in itself these functions should work (or am
I missing an elephant here).

I've also seen questions about formulas that only work when they
refer to
cells with straight numbers instead of formulas, but even when I
replace
the
'concatenate' results with the resulting value of the cell, the
result
stays
the same.

For another formula on another sheet to work we need to have this
error
sorted out, and so far I haven't found what causes this. Can you
point me
in
the right direction?

Thank you for this one. The SUMPRODUCT works like a charm.

Any chance of a similar solution for a SUMIF?
The second problem in this sheet is one with SUMIF, and it also triples
values by three, so it's a similar problem. To be complete I'll add the
formula:

=SUMIF('SHEETA'!AJ:AJ;D47;'SHEETA'!AL:AL)

where in SHEETA AJ is the column with the CONCENATE values that needs to
find a match with column D and AL is the sales for that month for that
combination in AJ/D....

I'll go check these forums again for this one right now ....

Thank you again.



"T. Valko" wrote:

Try using SUMPRODUCT.

CONCATENATE returns a text value *but* COUNTIF evalauates text numbers and
numeric numbers as being equal. That's probably why it's truncating the last
digit since Excel will only evaluate to 15 significant digits. You will
probably have to format your range $AJ$83:$AJ$4916 as TEXT if it isn't
already.

=SUMPRODUCT(--($AJ$83:$AJ$4916=AJ97))

--
Biff
Microsoft Excel MVP


"John C" <johnc@stateofdenial wrote in message
...
Could it be since your strings are all numbers, that there are just too
many
'significant' numbers for xl to deal with?
--
John C


"JusMe" wrote:

the result is a string of numbers .... like these:

50000356020079
500003560200710

the 'countif' one works with the first one, and doesn't seem to work with
the second one



"T. Valko" wrote:

=CONCATENATE(A97;G97;H97)
=CONCATENATE(A98;G98;H98)
the result of this is either 14 or 15 characters
(depending on the month of the year)

Sounds like you're "building" date strings.

Post some examples of the resulting strings from the above formulas.

--
Biff
Microsoft Excel MVP


"JusMe" wrote in message
...
Using Windows XP and Office2003:

In column AJ we have a formula
...
=CONCATENATE(A97;G97;H97)
=CONCATENATE(A98;G98;H98)
...

the result of this is either 14 or 15 characters (depending on the
month
of
the year)

in column AT we do a COUNTIF:
...
=COUNTIF($AJ$83:$AJ$4916;AJ97)
=COUNTIF($AJ$83:$AJ$4916;AJ98)

since all of the values in the AJ column are unique, all of these
formulas
should have "1" as a result, however, the ones with a string in the
AJ
column
of 15 characters give "3" as a result (conclusion: that's for months
10,
11
and 12 where the 0, 1 and 2 aren't recognized/counted).

This would mean that only the first 14 characters are evaluated. I've
tried
several workarounds, but in itself these functions should work (or am
I missing an elephant here).

I've also seen questions about formulas that only work when they
refer to
cells with straight numbers instead of formulas, but even when I
replace
the
'concatenate' results with the resulting value of the cell, the
result
stays
the same.

For another formula on another sheet to work we need to have this
error
sorted out, and so far I haven't found what causes this. Can you
point me
in
the right direction?

On Mon, 11 Aug 2008 22:20:00 -0700, JusMe
wrote:

Using Windows XP and Office2003:

In column AJ we have a formula
...
=CONCATENATE(A97;G97;H97)
=CONCATENATE(A98;G98;H98)
...

the result of this is either 14 or 15 characters (depending on the month of
the year)

in column AT we do a COUNTIF:
...
=COUNTIF($AJ$83:$AJ$4916;AJ97)
=COUNTIF($AJ$83:$AJ$4916;AJ98)

since all of the values in the AJ column are unique, all of these formulas
should have "1" as a result, however, the ones with a string in the AJ column
of 15 characters give "3" as a result (conclusion: that's for months 10, 11
and 12 where the 0, 1 and 2 aren't recognized/counted).

This would mean that only the first 14 characters are evaluated. I've tried
several workarounds, but in itself these functions should work (or am
I missing an elephant here).

I've also seen questions about formulas that only work when they refer to
cells with straight numbers instead of formulas, but even when I replace the
'concatenate' results with the resulting value of the cell, the result stays
the same.

For another formula on another sheet to work we need to have this error
sorted out, and so far I haven't found what causes this. Can you point me in
the right direction?

The COUNTIF function works on cells that contain formulas, so the problem lies
with your data, or with the result of your concatenation.

Try using the Formula Evaluation tool to go step-by-step through the countif
formula.
--ron

evaluating the formula doesn't give errors; it gives the correct string, but
it still gives '3' in each line where months 10, 11 or 12 are referenced ....

I need to get Excel to evaluate all 15 characters and not just the first 14,
or so it seems ....



"Ron Rosenfeld" wrote:

On Mon, 11 Aug 2008 22:20:00 -0700, JusMe
wrote:

Using Windows XP and Office2003:

In column AJ we have a formula
...
=CONCATENATE(A97;G97;H97)
=CONCATENATE(A98;G98;H98)
...

the result of this is either 14 or 15 characters (depending on the month of
the year)

in column AT we do a COUNTIF:
...
=COUNTIF($AJ$83:$AJ$4916;AJ97)
=COUNTIF($AJ$83:$AJ$4916;AJ98)

since all of the values in the AJ column are unique, all of these formulas
should have "1" as a result, however, the ones with a string in the AJ column
of 15 characters give "3" as a result (conclusion: that's for months 10, 11
and 12 where the 0, 1 and 2 aren't recognized/counted).

This would mean that only the first 14 characters are evaluated. I've tried
several workarounds, but in itself these functions should work (or am
I missing an elephant here).

I've also seen questions about formulas that only work when they refer to
cells with straight numbers instead of formulas, but even when I replace the
'concatenate' results with the resulting value of the cell, the result stays
the same.

For another formula on another sheet to work we need to have this error
sorted out, and so far I haven't found what causes this. Can you point me in
the right direction?

The COUNTIF function works on cells that contain formulas, so the problem lies
with your data, or with the result of your concatenation.

Try using the Formula Evaluation tool to go step-by-step through the countif
formula.
--ron

On Tue, 12 Aug 2008 03:41:01 -0700, JusMe
wrote:

evaluating the formula doesn't give errors; it gives the correct string, but
it still gives '3' in each line where months 10, 11 or 12 are referenced ....

I need to get Excel to evaluate all 15 characters and not just the first 14,
or so it seems ....

I would not have expected the formula to give an error. The next step is to
see what Excel is matching on.

A first try at that would be to use Edit/Find and copy paste the exact string
that your formula is generating into the Find What: box, and see if there are
multiple matches.

To copy/paste the exact string, I would

select the box with the concatenate formula that is giving the unexpected
result

Edit/Copy

Edit/Find

select Find What:

<ctrl-V (which should paste the value into the Find What box.

<Find All and a list of matching cells should appear, along with the value
contained in each cell.

If that doesn't work, we'll try a different method.

--ron

Ron,

Thank you for your time and patience.
Following what you typed, (changing the 'Look in' from Formulas to Values) I
get the right cells back, one for every command I give for any line ....




"Ron Rosenfeld" wrote:

On Tue, 12 Aug 2008 03:41:01 -0700, JusMe
wrote:

evaluating the formula doesn't give errors; it gives the correct string, but
it still gives '3' in each line where months 10, 11 or 12 are referenced ....

I need to get Excel to evaluate all 15 characters and not just the first 14,
or so it seems ....

I would not have expected the formula to give an error. The next step is to
see what Excel is matching on.

A first try at that would be to use Edit/Find and copy paste the exact string
that your formula is generating into the Find What: box, and see if there are
multiple matches.

To copy/paste the exact string, I would

select the box with the concatenate formula that is giving the unexpected
result

Edit/Copy

Edit/Find

select Find What:

<ctrl-V (which should paste the value into the Find What box.

<Find All and a list of matching cells should appear, along with the value
contained in each cell.

If that doesn't work, we'll try a different method.

--ron

On Tue, 12 Aug 2008 12:29:01 -0700, JusMe
wrote:

Ron,

Thank you for your time and patience.
Following what you typed, (changing the 'Look in' from Formulas to Values) I
get the right cells back, one for every command I give for any line ....


Well, for COUNTIF to return a '3', there must be three cells that it is
matching.

Since the Find didn't find them, then "easiest" next step would be to enter in,
for example A97;G97;H97 some values that, gives a result in your AJ97
concatenation formula so that your formula in column AT gives a result of 3.



Then, in some cell, e.g. AK83, enter the formula:

=AJ83=$AJ$97

and fill down to $AJ$4916.

Look to see which one's are TRUE.

Oh, I see you've switched to using SUMPRODUCT, so you may not want to track
this down any further. But that would be my next step, along with using
conditional formatting to highlight the TRUE result.
--ron