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?