PC Review


Reply
Thread Tools Rate Thread

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.
 
Reply With Quote
 
 
 
 
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
 
Reply With Quote
 
 
 
 
=?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
>

 
Reply With Quote
 
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
 
Reply With Quote
 
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
>>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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 Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count values, excluding duplicates and specific values eaemeric Microsoft Excel Worksheet Functions 2 19th Aug 2011 05:11 PM
Count total values excluding some data UT Microsoft Excel Misc 6 2nd Jun 2009 08:25 PM
how do i count specific text strings excluding blank cells? =?Utf-8?B?c2tpanNoMTk3OQ==?= Microsoft Excel Worksheet Functions 2 14th Jun 2007 06:37 AM
How to count blank cells (Rows) between Non-Blank cells in Col "A" Dennis Microsoft Excel Misc 8 20th Jul 2006 11:25 PM
How to count cells excluding repeat info mwfernandez Microsoft Excel Discussion 3 1st Sep 2004 03:26 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:31 PM.