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?