COUNT with Multiple Criteria Error

[Teri]

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)


"Teri" <Teri@discussions.microsoft.com> wrote in message
news:83B3A3DE-A6A0-400A-ACD7-3ED67AD60FF1@microsoft.com...
> 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!
>

[Teri]

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" <Teri@discussions.microsoft.com> wrote in message
> news:83B3A3DE-A6A0-400A-ACD7-3ED67AD60FF1@microsoft.com...
> > 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!
> >
>
>
>

[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

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" <Teri@discussions.microsoft.com> wrote in message
>>news:83B3A3DE-A6A0-400A-ACD7-3ED67AD60FF1@microsoft.com...
>>
>>>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!
>>>
>>
>>
>>

[Teri]

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" <Teri@discussions.microsoft.com> wrote in message
> >>news:83B3A3DE-A6A0-400A-ACD7-3ED67AD60FF1@microsoft.com...
> >>
> >>>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]

It's invariably rogue data :-))

--

HTH

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


"Teri" <Teri@discussions.microsoft.com> wrote in message
news:4B9AECA0-323C-40CB-A5A6-A879CF282440@microsoft.com...
> 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" <Teri@discussions.microsoft.com> wrote in message
> > >>news:83B3A3DE-A6A0-400A-ACD7-3ED67AD60FF1@microsoft.com...
> > >>
> > >>>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!
> > >>>
> > >>
> > >>
> > >>
> >