COUNT with Multiple Criteria Error

[Guest]

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!

 

[Bob Phillips]

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)

 

[Guest]

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?

 

[windsurferLA]

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

 

[Guest]

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!

 

[Bob Phillips]

It's invariably rogue data )

--

HTH

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