=SUMPRODUCT(--($D$5:$D$57=$D121),--(G$5:G$57))

[Dave F]

I'm stuck using XL 2000 and so can't figure out why this formula is not
calculating. D557 are expense codes, formatted as General. These expense
codes are 5 digits long, but are NOT formatted as numbers. D121 is an
expense code, formatted as General. G5:G57 are dollar values, formatted as
Accounting.

The value returned should be $1,203,201 but $0 is being returned.

Does XL 2000 have a SUMPRODUCT limitation that I'm unaware of?

Dave

 

[Dave Peterson]

First, changing the format of a cell won't change the value.

If the cell was formatted as text and you typed 12345, then changed the format
to General, the value will still be the text: 12345.

But if D557 are really text values, then try formatting D121 as text and
reenter the value. Then check the formula.

Or...
=SUMPRODUCT(--($D$5:$D$57=$D121&""),--(G$5:G$57))
or maybe...
=SUMPRODUCT(--($D$5:$D$57=text($D121,"00000")),--(G$5:G$57))

$d121&"" will coerce the number to text
and
=text($d121,"00000") will return a string with (maybe) leading 0's.

 

[Dave F]

Thanks for the suggestions but neither one works.

Oddly, if I copy a signle expense code from the range D557 and paste it in
D121, the formula works correctly!

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

First, changing the format of a cell won't change the value.

If the cell was formatted as text and you typed 12345, then changed the format
to General, the value will still be the text: 12345.

But if D557 are really text values, then try formatting D121 as text and
reenter the value. Then check the formula.

Or...
=SUMPRODUCT(--($D$5:$D$57=$D121&""),--(G$5:G$57))
or maybe...
=SUMPRODUCT(--($D$5:$D$57=text($D121,"00000")),--(G$5:G$57))

$d121&"" will coerce the number to text
and
=text($d121,"00000") will return a string with (maybe) leading 0's.

 

[Dave Peterson]

Find 4 empty cells:

=count(d5:d57)
=counta(d5:d57)

=count(d121)
=counta(d121)

If all your entries are numbers, then each pair should match.

You may even want to do:
=count(g5:g57)
and compare it to:
=counta(g5:g57)

if the values in G5:G57 are really numbers, you don't need the --() stuff.

Thanks for the suggestions but neither one works.

Oddly, if I copy a signle expense code from the range D557 and paste it in
D121, the formula works correctly!

Dave

 

[T. Valko]

That leads me to believe that the formats are not the same. When you copy
from the range D557 and paste into D121 you're also copying and pasting
the format of the source (unless you do a paste>special and exclude the
format). And, as you say, when you do that the formula then works.

Dave P. explained that simply changing a format does not apply until the
target cell is edited.

Biff

Thanks for the suggestions but neither one works.

Oddly, if I copy a signle expense code from the range D557 and paste it
in
D121, the formula works correctly!

Dave