How do I use countif to count values excluding blank cells

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

  1. Guest

    Guest Guest

    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
    #1
    1. Advertisements

  2. =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
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    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
    #3
  4. 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
    #4
  5. Guest

    PCLIVE Guest

    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
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Guest

    Automatic copying data excluding blank cells

    Guest, Nov 25, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    6
    Views:
    395
  2. Replies:
    2
    Views:
    373
  3. Replies:
    2
    Views:
    458
  4. Guest

    Counting non-blank cells in a column, excluding hidden rows

    Guest, Jun 1, 2007, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    413
    Don Guillett
    Jun 1, 2007
  5. Guest

    how do i count specific text strings excluding blank cells?

    Guest, Jun 13, 2007, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    472
    Guest
    Jun 14, 2007
  6. ken

    use "countif" function to count cells by colour

    ken, Dec 12, 2007, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    351
    Carim
    Dec 12, 2007
  7. aganoe

    how to count#cells w/= value in other column and not count blank c

    aganoe, Apr 8, 2010, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    714
    Stefi
    Apr 9, 2010
  8. eaemeric

    Count values, excluding duplicates and specific values

    eaemeric, Aug 18, 2011, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    527
    eaemeric
    Aug 19, 2011
Loading...