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?

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

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?

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

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?

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

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?

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

The SUMPRODUCT equivalent is:

=SUMPRODUCT(--(SheetA!AJ1:AJ100=D47);SheetA!AL1:AL100)

Note that with SUMPRODUCT you *can't* use entire columns as range references
unless you're using Excel 2007. So, unless you're using Excel 2007 you have
to use a specific range.

--
Biff
Microsoft Excel MVP


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

That formula ends in a # N/A in all cells/calculations. Evaluation of the
formula doesn't really give me an indication of where things go wrong.

"T. Valko" wrote:

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

The SUMPRODUCT equivalent is:

=SUMPRODUCT(--(SheetA!AJ1:AJ100=D47);SheetA!AL1:AL100)

Note that with SUMPRODUCT you *can't* use entire columns as range references
unless you're using Excel 2007. So, unless you're using Excel 2007 you have
to use a specific range.

--
Biff
Microsoft Excel MVP


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

Are there any #N/A errors in any of the referenced ranges?


--
Biff
Microsoft Excel MVP


"JusMe" wrote in message
...
That formula ends in a # N/A in all cells/calculations. Evaluation of the
formula doesn't really give me an indication of where things go wrong.

"T. Valko" wrote:

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

The SUMPRODUCT equivalent is:

=SUMPRODUCT(--(SheetA!AJ1:AJ100=D47);SheetA!AL1:AL100)

Note that with SUMPRODUCT you *can't* use entire columns as range
references
unless you're using Excel 2007. So, unless you're using Excel 2007 you
have
to use a specific range.

--
Biff
Microsoft Excel MVP


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

no errors, all cells contain data and the 'sumif' formula works for all
(except for all of the wrong (tripled) values in the 10th, 11th and 12th
months).

"T. Valko" wrote:

Are there any #N/A errors in any of the referenced ranges?


--
Biff
Microsoft Excel MVP


"JusMe" wrote in message
...
That formula ends in a # N/A in all cells/calculations. Evaluation of the
formula doesn't really give me an indication of where things go wrong.

"T. Valko" wrote:

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

The SUMPRODUCT equivalent is:

=SUMPRODUCT(--(SheetA!AJ1:AJ100=D47);SheetA!AL1:AL100)

Note that with SUMPRODUCT you *can't* use entire columns as range
references
unless you're using Excel 2007. So, unless you're using Excel 2007 you
have
to use a specific range.

--
Biff
Microsoft Excel MVP


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

At this point there's not much more I can suggest.

I'd need to be able to see the file to figure out what's going wrong.

If you want to, you can upload a small sample file to a free file host that
shows the problem . There are several available. One I use often is:

http://translate.google.com/translat...l%3Den%26lr%3D

It's a French site that gets translated to English. Note there's a file size
limit and the file is removed after a few weeks.

--
Biff
Microsoft Excel MVP


"JusMe" wrote in message
...
no errors, all cells contain data and the 'sumif' formula works for all
(except for all of the wrong (tripled) values in the 10th, 11th and 12th
months).

"T. Valko" wrote:

Are there any #N/A errors in any of the referenced ranges?


--
Biff
Microsoft Excel MVP


"JusMe" wrote in message
...
That formula ends in a # N/A in all cells/calculations. Evaluation of
the
formula doesn't really give me an indication of where things go wrong.

"T. Valko" wrote:

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

The SUMPRODUCT equivalent is:

=SUMPRODUCT(--(SheetA!AJ1:AJ100=D47);SheetA!AL1:AL100)

Note that with SUMPRODUCT you *can't* use entire columns as range
references
unless you're using Excel 2007. So, unless you're using Excel 2007 you
have
to use a specific range.

--
Biff
Microsoft Excel MVP


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

well, the first part works, now I have to get the second part going ....

right now I'm not getting anywhere trying your suggestion (not getting TRUE
anywhere yet) - I probably have been looking at this thing for way too long,
I'll try again tomorrow and get back to you.



"Ron Rosenfeld" wrote:

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

The result of the SUMIF is the total amount of realized sales for that
particular item (and sumproduct doesn't work for that, right?). So I need to
figure out how to get that transferred to something that works ....

to hopefully be more clear: column AL contains amounts that need to be added
up if the concatenated number in AJ of the other sheet is equal to that in D
for that line....


* I feel like I need to find me a good training .... *
I can't stand not 'getting' these things like I want to!



"T. Valko" wrote:

At this point there's not much more I can suggest.

I'd need to be able to see the file to figure out what's going wrong.

If you want to, you can upload a small sample file to a free file host that
shows the problem . There are several available. One I use often is:

http://translate.google.com/translat...l%3Den%26lr%3D

It's a French site that gets translated to English. Note there's a file size
limit and the file is removed after a few weeks.

--
Biff
Microsoft Excel MVP


"JusMe" wrote in message
...
no errors, all cells contain data and the 'sumif' formula works for all
(except for all of the wrong (tripled) values in the 10th, 11th and 12th
months).

"T. Valko" wrote:

Are there any #N/A errors in any of the referenced ranges?


--
Biff
Microsoft Excel MVP


"JusMe" wrote in message
...
That formula ends in a # N/A in all cells/calculations. Evaluation of
the
formula doesn't really give me an indication of where things go wrong.

"T. Valko" wrote:

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

The SUMPRODUCT equivalent is:

=SUMPRODUCT(--(SheetA!AJ1:AJ100=D47);SheetA!AL1:AL100)

Note that with SUMPRODUCT you *can't* use entire columns as range
references
unless you're using Excel 2007. So, unless you're using Excel 2007 you
have
to use a specific range.

--
Biff
Microsoft Excel MVP


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

The SUMPRODUCT formula is useable replacement for SUMIF and should have
worked. At the very least, it should have returned a result of 0 and not
#N/A.

I can't troubleshoot this without seeing it for myself.

If you're comparing the result of a CONCATENATE formula to other strings of
digits it won't work because one is TEXT (CONCATENATE) and the other may or
may not be a number. You have to make sure both data types are the same.

--
Biff
Microsoft Excel MVP


"JusMe" wrote in message
...

The result of the SUMIF is the total amount of realized sales for that
particular item (and sumproduct doesn't work for that, right?). So I need
to
figure out how to get that transferred to something that works ....

to hopefully be more clear: column AL contains amounts that need to be
added
up if the concatenated number in AJ of the other sheet is equal to that in
D
for that line....


* I feel like I need to find me a good training .... *
I can't stand not 'getting' these things like I want to!



"T. Valko" wrote:

At this point there's not much more I can suggest.

I'd need to be able to see the file to figure out what's going wrong.

If you want to, you can upload a small sample file to a free file host
that
shows the problem . There are several available. One I use often is:

http://translate.google.com/translat...l%3Den%26lr%3D

It's a French site that gets translated to English. Note there's a file
size
limit and the file is removed after a few weeks.

--
Biff
Microsoft Excel MVP


"JusMe" wrote in message
...
no errors, all cells contain data and the 'sumif' formula works for all
(except for all of the wrong (tripled) values in the 10th, 11th and
12th
months).

"T. Valko" wrote:

Are there any #N/A errors in any of the referenced ranges?


--
Biff
Microsoft Excel MVP


"JusMe" wrote in message
...
That formula ends in a # N/A in all cells/calculations. Evaluation
of
the
formula doesn't really give me an indication of where things go
wrong.

"T. Valko" wrote:

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

The SUMPRODUCT equivalent is:

=SUMPRODUCT(--(SheetA!AJ1:AJ100=D47);SheetA!AL1:AL100)

Note that with SUMPRODUCT you *can't* use entire columns as range
references
unless you're using Excel 2007. So, unless you're using Excel 2007
you
have
to use a specific range.

--
Biff
Microsoft Excel MVP


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

Even with only sending 24 lines in both sheets, the file is too big, I'm
trying to get it down to 500K so I can upload it ....

"T. Valko" wrote:

The SUMPRODUCT formula is useable replacement for SUMIF and should have
worked. At the very least, it should have returned a result of 0 and not
#N/A.

I can't troubleshoot this without seeing it for myself.

If you're comparing the result of a CONCATENATE formula to other strings of
digits it won't work because one is TEXT (CONCATENATE) and the other may or
may not be a number. You have to make sure both data types are the same.

--
Biff
Microsoft Excel MVP


"JusMe" wrote in message
...

The result of the SUMIF is the total amount of realized sales for that
particular item (and sumproduct doesn't work for that, right?). So I need
to
figure out how to get that transferred to something that works ....

to hopefully be more clear: column AL contains amounts that need to be
added
up if the concatenated number in AJ of the other sheet is equal to that in
D
for that line....


* I feel like I need to find me a good training .... *
I can't stand not 'getting' these things like I want to!



"T. Valko" wrote:

At this point there's not much more I can suggest.

I'd need to be able to see the file to figure out what's going wrong.

If you want to, you can upload a small sample file to a free file host
that
shows the problem . There are several available. One I use often is:

http://translate.google.com/translat...l%3Den%26lr%3D

It's a French site that gets translated to English. Note there's a file
size
limit and the file is removed after a few weeks.

--
Biff
Microsoft Excel MVP


"JusMe" wrote in message
...
no errors, all cells contain data and the 'sumif' formula works for all
(except for all of the wrong (tripled) values in the 10th, 11th and
12th
months).

"T. Valko" wrote:

Are there any #N/A errors in any of the referenced ranges?


--
Biff
Microsoft Excel MVP


"JusMe" wrote in message
...
That formula ends in a # N/A in all cells/calculations. Evaluation
of
the
formula doesn't really give me an indication of where things go
wrong.

"T. Valko" wrote:

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

The SUMPRODUCT equivalent is:

=SUMPRODUCT(--(SheetA!AJ1:AJ100=D47);SheetA!AL1:AL100)

Note that with SUMPRODUCT you *can't* use entire columns as range
references
unless you're using Excel 2007. So, unless you're using Excel 2007
you
have
to use a specific range.

--
Biff
Microsoft Excel MVP


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

Do you a file compression utility? Try zipping the file. I can only open
*.zip files.

--
Biff
Microsoft Excel MVP


"JusMe" wrote in message
...
Even with only sending 24 lines in both sheets, the file is too big, I'm
trying to get it down to 500K so I can upload it ....

"T. Valko" wrote:

The SUMPRODUCT formula is useable replacement for SUMIF and should have
worked. At the very least, it should have returned a result of 0 and not
#N/A.

I can't troubleshoot this without seeing it for myself.

If you're comparing the result of a CONCATENATE formula to other strings
of
digits it won't work because one is TEXT (CONCATENATE) and the other may
or
may not be a number. You have to make sure both data types are the same.

--
Biff
Microsoft Excel MVP


"JusMe" wrote in message
...

The result of the SUMIF is the total amount of realized sales for that
particular item (and sumproduct doesn't work for that, right?). So I
need
to
figure out how to get that transferred to something that works ....

to hopefully be more clear: column AL contains amounts that need to be
added
up if the concatenated number in AJ of the other sheet is equal to that
in
D
for that line....


* I feel like I need to find me a good training .... *
I can't stand not 'getting' these things like I want to!



"T. Valko" wrote:

At this point there's not much more I can suggest.

I'd need to be able to see the file to figure out what's going wrong.

If you want to, you can upload a small sample file to a free file host
that
shows the problem . There are several available. One I use often is:

http://translate.google.com/translat...l%3Den%26lr%3D

It's a French site that gets translated to English. Note there's a
file
size
limit and the file is removed after a few weeks.

--
Biff
Microsoft Excel MVP


"JusMe" wrote in message
...
no errors, all cells contain data and the 'sumif' formula works for
all
(except for all of the wrong (tripled) values in the 10th, 11th and
12th
months).

"T. Valko" wrote:

Are there any #N/A errors in any of the referenced ranges?


--
Biff
Microsoft Excel MVP


"JusMe" wrote in message
...
That formula ends in a # N/A in all cells/calculations.
Evaluation
of
the
formula doesn't really give me an indication of where things go
wrong.

"T. Valko" wrote:

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

The SUMPRODUCT equivalent is:

=SUMPRODUCT(--(SheetA!AJ1:AJ100=D47);SheetA!AL1:AL100)

Note that with SUMPRODUCT you *can't* use entire columns as
range
references
unless you're using Excel 2007. So, unless you're using Excel
2007
you
have
to use a specific range.

--
Biff
Microsoft Excel MVP


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

when copying part of the document into an empty sheet it seems the sumproduct
is working *no doubt due to the fact that values were pasted*

So now I'll first copy the original formulas into the new sheet one by one
and see if that will work. I hope I can see when things go wrong that way ....
I'll 'report back' how things are going

"T. Valko" wrote:

Do you a file compression utility? Try zipping the file. I can only open
*.zip files.

--
Biff
Microsoft Excel MVP


"JusMe" wrote in message
...
Even with only sending 24 lines in both sheets, the file is too big, I'm
trying to get it down to 500K so I can upload it ....

"T. Valko" wrote:

The SUMPRODUCT formula is useable replacement for SUMIF and should have
worked. At the very least, it should have returned a result of 0 and not
#N/A.

I can't troubleshoot this without seeing it for myself.

If you're comparing the result of a CONCATENATE formula to other strings
of
digits it won't work because one is TEXT (CONCATENATE) and the other may
or
may not be a number. You have to make sure both data types are the same.

--
Biff
Microsoft Excel MVP


"JusMe" wrote in message
...

The result of the SUMIF is the total amount of realized sales for that
particular item (and sumproduct doesn't work for that, right?). So I
need
to
figure out how to get that transferred to something that works ....

to hopefully be more clear: column AL contains amounts that need to be
added
up if the concatenated number in AJ of the other sheet is equal to that
in
D
for that line....


* I feel like I need to find me a good training .... *
I can't stand not 'getting' these things like I want to!



"T. Valko" wrote:

At this point there's not much more I can suggest.

I'd need to be able to see the file to figure out what's going wrong.

If you want to, you can upload a small sample file to a free file host
that
shows the problem . There are several available. One I use often is:

http://translate.google.com/translat...l%3Den%26lr%3D

It's a French site that gets translated to English. Note there's a
file
size
limit and the file is removed after a few weeks.

--
Biff
Microsoft Excel MVP


"JusMe" wrote in message
...
no errors, all cells contain data and the 'sumif' formula works for
all
(except for all of the wrong (tripled) values in the 10th, 11th and
12th
months).

"T. Valko" wrote:

Are there any #N/A errors in any of the referenced ranges?


--
Biff
Microsoft Excel MVP


"JusMe" wrote in message
...
That formula ends in a # N/A in all cells/calculations.
Evaluation
of
the
formula doesn't really give me an indication of where things go
wrong.

"T. Valko" wrote:

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

The SUMPRODUCT equivalent is:

=SUMPRODUCT(--(SheetA!AJ1:AJ100=D47);SheetA!AL1:AL100)

Note that with SUMPRODUCT you *can't* use entire columns as
range
references
unless you're using Excel 2007. So, unless you're using Excel
2007
you
have
to use a specific range.

--
Biff
Microsoft Excel MVP


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