Countif formula - simplify

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

benny - 12 Sep 2005 10:41 GMT

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

Reply to this Message

Max - 12 Sep 2005 10:49 GMT

One way

Try: =SUMPRODUCT((B2:B50>1)*(B2:B50<=3))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

> Hi
> I have a problem to make simple countif formula for more than 1 category.
[quoted text clipped - 10 lines]
>
> thanks

Reply to this Message

Max - 12 Sep 2005 23:24 GMT

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
--

Reply to this Message

Bob Phillips - 12 Sep 2005 13:01 GMT

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

if integer strings as you show.

Signature

HTH

Bob Phillips

> Hi
> I have a problem to make simple countif formula for more than 1 category.
[quoted text clipped - 10 lines]
>
> thanks

Reply to this Message

Ron Rosenfeld - 12 Sep 2005 13:09 GMT

>Hi
>I have a problem to make simple countif formula for more than 1 category.
[quoted text clipped - 10 lines]
>
>thanks

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

--ron

Reply to this Message

Tom Ogilvy - 12 Sep 2005 15:19 GMT

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

Signature

Regards,
Tom Ogilvy

> Hi
> I have a problem to make simple countif formula for more than 1 category.
[quoted text clipped - 10 lines]
>
> thanks

Reply to this Message

benny - 13 Sep 2005 01:42 GMT

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

\benny

> =SUM(COUNTIF(B2:B50,{1,2,3}))
>
[quoted text clipped - 16 lines]
> >
> > thanks

Reply to this Message