How do I use countif to count values excluding blank cells

Discussion in 'Microsoft Excel Worksheet Functions' started by Guest, Jan 30, 2006.

1. GuestGuest

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.

Guest, Jan 30, 2006

2. Dave PetersonGuest

=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, Jan 30, 2006

3. GuestGuest

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
>

Guest, Jan 30, 2006
4. Dave PetersonGuest

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

Dave Peterson, Jan 30, 2006
5. PCLIVEGuest

I thought maybe something like:

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

"Glenda" <> wrote in message
news:...
> 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
>>

PCLIVE, Jan 30, 2006