Countif formula - simplify

[benny]

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

 

[Max]

One way

Try: =SUMPRODUCT((B2:B50>1)*(B2:B50<=3))

 

[Bob Phillips]

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

if integer strings as you show.

 

[Ron Rosenfeld]

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

 

[Tom Ogilvy]

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

 

[Max]

Oops, missed out 1 equal sign, should be:
=SUMPRODUCT((B2:B50>=1)*(B2:B50<=3))

 

[benny]

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

\benny