countif on cells with formula's won't work correctly

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