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?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Automatic copying data excluding blank cells

    Guest, Nov 25, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    6
    Views:
    213
  2. Replies:
    2
    Views:
    248
  3. Guest

    how do i count specific text strings excluding blank cells?

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

    use "countif" function to count cells by colour

    ken, Dec 12, 2007, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    231
    Carim
    Dec 12, 2007
  5. eaemeric

    Count values, excluding duplicates and specific values

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

Share This Page