Hi
I have a problem to make simple countif formula for more than 1 category.
Suppose I have an array of data in B2:B50 which has value (1 to 9). I want
to count the number of value with categories where the value is equal or
more than 1 and equal or less than 3.
I make the formula
"=Countif(B2:B50,"1")+Countif(B2:B50,"2")+Countif(B2:B50,"3")...that looks
so long formula.

I want to make it simple with just one countif formula...without "+"
operator.
Highly appreciate for some help

thanks

One way

Try: =SUMPRODUCT((B2:B50>1)*(B2:B50<=3))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"benny" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> Hi
> I have a problem to make simple countif formula for more than 1 category.
> Suppose I have an array of data in B2:B50 which has value (1 to 9). I want
> to count the number of value with categories where the value is equal or
> more than 1 and equal or less than 3.
> I make the formula
> "=Countif(B2:B50,"1")+Countif(B2:B50,"2")+Countif(B2:B50,"3")...that looks
> so long formula.
>
> I want to make it simple with just one countif formula...without "+"
> operator.
> Highly appreciate for some help
>
> thanks

=SUMPRODUCT((B2:B50={"1","2","3"})+0)

if integer strings as you show.

--
HTH

Bob Phillips

"benny" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Hi
> I have a problem to make simple countif formula for more than 1 category.
> Suppose I have an array of data in B2:B50 which has value (1 to 9). I want
> to count the number of value with categories where the value is equal or
> more than 1 and equal or less than 3.
> I make the formula
> "=Countif(B2:B50,"1")+Countif(B2:B50,"2")+Countif(B2:B50,"3")...that looks
> so long formula.
>
> I want to make it simple with just one countif formula...without "+"
> operator.
> Highly appreciate for some help
>
> thanks
>
>

On Mon, 12 Sep 2005 16:41:58 +0700, "benny" <(E-Mail Removed)> wrote:

>Hi
>I have a problem to make simple countif formula for more than 1 category.
>Suppose I have an array of data in B2:B50 which has value (1 to 9). I want
>to count the number of value with categories where the value is equal or
>more than 1 and equal or less than 3.
>I make the formula
>"=Countif(B2:B50,"1")+Countif(B2:B50,"2")+Countif(B2:B50,"3")...that looks
>so long formula.
>
>I want to make it simple with just one countif formula...without "+"
>operator.
>Highly appreciate for some help
>
>thanks
>

=COUNTIF(B2:B50,">=1") - COUNTIF(B2:B50,">3")


--ron

=SUM(COUNTIF(B2:B50,{1,2,3}))

--
Regards,
Tom Ogilvy

"benny" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Hi
> I have a problem to make simple countif formula for more than 1 category.
> Suppose I have an array of data in B2:B50 which has value (1 to 9). I want
> to count the number of value with categories where the value is equal or
> more than 1 and equal or less than 3.
> I make the formula
> "=Countif(B2:B50,"1")+Countif(B2:B50,"2")+Countif(B2:B50,"3")...that looks
> so long formula.
>
> I want to make it simple with just one countif formula...without "+"
> operator.
> Highly appreciate for some help
>
> thanks
>
>

Oops, missed out 1 equal sign, should be:
=SUMPRODUCT((B2:B50>=1)*(B2:B50<=3))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

It works..great, many thanks for the idea.

\benny

"Tom Ogilvy" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> =SUM(COUNTIF(B2:B50,{1,2,3}))
>
> --
> Regards,
> Tom Ogilvy
>
> "benny" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > Hi
> > I have a problem to make simple countif formula for more than 1
category.
> > Suppose I have an array of data in B2:B50 which has value (1 to 9). I
want
> > to count the number of value with categories where the value is equal or
> > more than 1 and equal or less than 3.
> > I make the formula
> > "=Countif(B2:B50,"1")+Countif(B2:B50,"2")+Countif(B2:B50,"3")...that
looks
> > so long formula.
> >
> > I want to make it simple with just one countif formula...without "+"
> > operator.
> > Highly appreciate for some help
> >
> > thanks
> >
> >
>
>