# How do I use countif to count values excluding blank cells

=?Utf-8?B?R2xlbmRh?=
Guest
Posts: n/a

 30th Jan 2006
I am trying to get the percentage of 1's in a column excluding blank cells

A B
1 1 0
2 1 1
3
4 0

I know the formula would be =countif(a1:a4,1) answer would be 2 but, how do
I get the percent of the #1's with including blank cells.

Dave Peterson
Guest
Posts: n/a

 30th Jan 2006
=countif(a1:a4,1)/count(a1:a4)

=count() returns the number of numbers in the range.

Glenda wrote:
>
> I am trying to get the percentage of 1's in a column excluding blank cells
>
> A B
> 1 1 0
> 2 1 1
> 3
> 4 0
>
> I know the formula would be =countif(a1:a4,1) answer would be 2 but, how do
> I get the percent of the #1's with including blank cells.

--

Dave Peterson

=?Utf-8?B?R2xlbmRh?=
Guest
Posts: n/a

 30th Jan 2006
Okay, now what If I want to exclude the blank cell in a3

"Dave Peterson" wrote:

> =countif(a1:a4,1)/count(a1:a4)
>
> =count() returns the number of numbers in the range.
>
> Glenda wrote:
> >
> > I am trying to get the percentage of 1's in a column excluding blank cells
> >
> > A B
> > 1 1 0
> > 2 1 1
> > 3
> > 4 0
> >
> > I know the formula would be =countif(a1:a4,1) answer would be 2 but, how do
> > I get the percent of the #1's with including blank cells.

>
> --
>
> Dave Peterson
>

Dave Peterson
Guest
Posts: n/a

 30th Jan 2006
Since the cell is empty, it won't be included in the =countif() and it won't be
included in the =count().

In fact, all non-numeric entries would be excluded from the =count() portion.

Am I missing something?

Glenda wrote:
>
> Okay, now what If I want to exclude the blank cell in a3
>
> "Dave Peterson" wrote:
>
> > =countif(a1:a4,1)/count(a1:a4)
> >
> > =count() returns the number of numbers in the range.
> >
> > Glenda wrote:
> > >
> > > I am trying to get the percentage of 1's in a column excluding blank cells
> > >
> > > A B
> > > 1 1 0
> > > 2 1 1
> > > 3
> > > 4 0
> > >
> > > I know the formula would be =countif(a1:a4,1) answer would be 2 but, how do
> > > I get the percent of the #1's with including blank cells.

> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

PCLIVE
Guest
Posts: n/a

 30th Jan 2006
I thought maybe something like:

=COUNTIF(A1:A4,A1)/COUNTA(A1:A4)

"Glenda" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Okay, now what If I want to exclude the blank cell in a3
>
> "Dave Peterson" wrote:
>
>> =countif(a1:a4,1)/count(a1:a4)
>>
>> =count() returns the number of numbers in the range.
>>
>> Glenda wrote:
>> >
>> > I am trying to get the percentage of 1's in a column excluding blank
>> > cells
>> >
>> > A B
>> > 1 1 0
>> > 2 1 1
>> > 3
>> > 4 0
>> >
>> > I know the formula would be =countif(a1:a4,1) answer would be 2 but,
>> > how do
>> > I get the percent of the #1's with including blank cells.

>>
>> --
>>
>> Dave Peterson
>>

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post eaemeric Microsoft Excel Worksheet Functions 2 19th Aug 2011 05:11 PM UT Microsoft Excel Misc 6 2nd Jun 2009 08:25 PM =?Utf-8?B?c2tpanNoMTk3OQ==?= Microsoft Excel Worksheet Functions 2 14th Jun 2007 06:37 AM Dennis Microsoft Excel Misc 8 20th Jul 2006 11:25 PM mwfernandez Microsoft Excel Discussion 3 1st Sep 2004 03:26 PM

Features