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

J

JusMe

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?
 
T

T. Valko

=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)
Click to expand...

Sounds like you're "building" date strings.

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

Ron Rosenfeld

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?
Click to expand...

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
 
J

JusMe

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
 
J

JusMe

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 ....
 
R

Ron Rosenfeld

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 ....
Click to expand...

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
 
J

John C

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

T. Valko

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))
 
J

JusMe

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 ....
 
J

JusMe

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

T. Valko

=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.
 
R

Ron Rosenfeld

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 ....
Click to expand...

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
 
J

JusMe

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.
 
J

JusMe

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

T. Valko

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/transla...m=1&ct=result&prev=/search?q=cjoint&hl=en&lr=

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.
 
J

JusMe

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.
 
J

JusMe

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

T. Valko

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.
 
J

JusMe

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 ....
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Countif Limitations 5
Any idea how to work? 0
Formula's won't read copy / pasted data ? 2
Help to work this out...... 1
Countif with dates for vs 2003 8
COUNTIF with multiple criteria doesn't work 5
Using a COUNTIF statement with an AND operator 4
Combination of IF, AND, and COUNTIF; need help 6

Top