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

I'm stuck using XL 2000 and so can't figure out why this formula is not
calculating. D5:D57 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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

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

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 D5:D57 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 wrote:

I'm stuck using XL 2000 and so can't figure out why this formula is not
calculating. D5:D57 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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

--

Dave Peterson

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

Thanks for the suggestions but neither one works.

Oddly, if I copy a signle expense code from the range D5:D57 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.


"Dave Peterson" wrote:

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 D5:D57 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 wrote:

I'm stuck using XL 2000 and so can't figure out why this formula is not
calculating. D5:D57 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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

--

Dave Peterson

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

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.

Dave F wrote:

Thanks for the suggestions but neither one works.

Oddly, if I copy a signle expense code from the range D5:D57 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.

"Dave Peterson" wrote:

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 D5:D57 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 wrote:

I'm stuck using XL 2000 and so can't figure out why this formula is not
calculating. D5:D57 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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

--

Dave Peterson


--

Dave Peterson

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

That leads me to believe that the formats are not the same. When you copy
from the range D5:D57 and paste into D121 you're also copying and pasting
the format of the source (unless you do a pastespecial 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

"Dave F" wrote in message
...
Thanks for the suggestions but neither one works.

Oddly, if I copy a signle expense code from the range D5:D57 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.


"Dave Peterson" wrote:

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 D5:D57 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 wrote:

I'm stuck using XL 2000 and so can't figure out why this formula is not
calculating. D5:D57 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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

--

Dave Peterson