I have the current formula:

=SUMPRODUCT(--($A$1:$A$2500="CLE"),--($B$1:$B$2500="IE"),--($E$1:$E$2500=6),--($I$1:$I$2500<0))

which works perfectly. Now I want to find out the number of times a value
less than 20% appears in column J. Why won't this work when I change the
formula so the ending argument is
,--($J$1:$J$2500<.20)) ??

I keep getting a #DIV/0 error!

Works okay for me in a small test.Try reducing the number of rows, and
increase until you get an error, then check the data.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Teri" wrote in message
...
I have the current formula:


=SUMPRODUCT(--($A$1:$A$2500="CLE"),--($B$1:$B$2500="IE"),--($E$1:$E$2500=6),
--($I$1:$I$2500<0))

which works perfectly. Now I want to find out the number of times a value
less than 20% appears in column J. Why won't this work when I change the
formula so the ending argument is
,--($J$1:$J$2500<.20)) ??

I keep getting a #DIV/0 error!

You're right! It does work if I decrease the range. It works all the way
up to $2200, but I have data in rows $2201 through $2500 that have values
less than 20%. How can I make this work?

"Bob Phillips" wrote:

Works okay for me in a small test.Try reducing the number of rows, and
increase until you get an error, then check the data.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Teri" wrote in message
...
I have the current formula:


=SUMPRODUCT(--($A$1:$A$2500="CLE"),--($B$1:$B$2500="IE"),--($E$1:$E$2500=6),
--($I$1:$I$2500<0))

which works perfectly. Now I want to find out the number of times a value
less than 20% appears in column J. Why won't this work when I change the
formula so the ending argument is
,--($J$1:$J$2500<.20)) ??

I keep getting a #DIV/0 error!

Going back to Bob Phillips comment, I'd check the lower J column cells
for the presence of data that does not allow a less-than test. Perhaps
you have numbers that have been entered as characters. The "Cntrl-`"
keys may help to view cell formulas.

WindsurferLA

Teri wrote:
You're right! It does work if I decrease the range. It works all the way
up to $2200, but I have data in rows $2201 through $2500 that have values
less than 20%. How can I make this work?

"Bob Phillips" wrote:


Works okay for me in a small test.Try reducing the number of rows, and
increase until you get an error, then check the data.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Teri" wrote in message
...

I have the current formula:



=SUMPRODUCT(--($A$1:$A$2500="CLE"),--($B$1:$B$2500="IE"),--($E$1:$E$2500=6),
--($I$1:$I$2500<0))

which works perfectly. Now I want to find out the number of times a value
less than 20% appears in column J. Why won't this work when I change the
formula so the ending argument is
,--($J$1:$J$2500<.20)) ??

I keep getting a #DIV/0 error!

Well, I'll be doggoned! There was one lousy cell (in 2400+ cells) that had
an error message in it. I corrected it, and VOILA! Works!

Thanks to both of you!

"windsurferLA" wrote:

Going back to Bob Phillips comment, I'd check the lower J column cells
for the presence of data that does not allow a less-than test. Perhaps
you have numbers that have been entered as characters. The "Cntrl-`"
keys may help to view cell formulas.

WindsurferLA

Teri wrote:
You're right! It does work if I decrease the range. It works all the way
up to $2200, but I have data in rows $2201 through $2500 that have values
less than 20%. How can I make this work?

"Bob Phillips" wrote:


Works okay for me in a small test.Try reducing the number of rows, and
increase until you get an error, then check the data.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Teri" wrote in message
...

I have the current formula:



=SUMPRODUCT(--($A$1:$A$2500="CLE"),--($B$1:$B$2500="IE"),--($E$1:$E$2500=6),
--($I$1:$I$2500<0))

which works perfectly. Now I want to find out the number of times a value
less than 20% appears in column J. Why won't this work when I change the
formula so the ending argument is
,--($J$1:$J$2500<.20)) ??

I keep getting a #DIV/0 error!

It's invariably rogue data :-))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Teri" wrote in message
...
Well, I'll be doggoned! There was one lousy cell (in 2400+ cells) that
had
an error message in it. I corrected it, and VOILA! Works!

Thanks to both of you!

"windsurferLA" wrote:

Going back to Bob Phillips comment, I'd check the lower J column cells
for the presence of data that does not allow a less-than test. Perhaps
you have numbers that have been entered as characters. The "Cntrl-`"
keys may help to view cell formulas.

WindsurferLA

Teri wrote:
You're right! It does work if I decrease the range. It works all
the way
up to $2200, but I have data in rows $2201 through $2500 that have
values
less than 20%. How can I make this work?

"Bob Phillips" wrote:


Works okay for me in a small test.Try reducing the number of rows, and
increase until you get an error, then check the data.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Teri" wrote in message
...

I have the current formula:




=SUMPRODUCT(--($A$1:$A$2500="CLE"),--($B$1:$B$2500="IE"),--($E$1:$E$2500=6
),
--($I$1:$I$2500<0))

which works perfectly. Now I want to find out the number of times a
value
less than 20% appears in column J. Why won't this work when I change
the
formula so the ending argument is
,--($J$1:$J$2500<.20)) ??

I keep getting a #DIV/0 error!