Power Query Course in Spanish
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: counting the x;s and the ci's

  1. #1
    New Member
    Join Date
    Feb 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post counting the x;s and the ci's

    Good evening I am in need of guidance. I have a spreadsheet that I need in each column the total number of marks / total number of x.
    CI
    x
    x
    x
    x
    005/004
    I want to enter a formula that will give me the above ansewer. Help me please.

  2. #2
    Board Regular Drrellik's Avatar
    Join Date
    Apr 2013
    Location
    East Coast, South Carolina
    Posts
    717
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: counting the x;s and the ci's

    =COUNTA(A1:A5)&"/"&COUNTIF(A1:A5,"X")

    where you data in in A1-A5
    There are 10 types of people in this world, those who understand binary, and those that dont.


    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  3. #3
    Board Regular Flashbond's Avatar
    Join Date
    Mar 2010
    Location
    Turkey
    Posts
    653
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: counting the x;s and the ci's

    =COUNTIF(CI:CI,"x")

    ?
    Last edited by Flashbond; Feb 21st, 2018 at 11:37 PM.

  4. #4
    Board Regular Drrellik's Avatar
    Join Date
    Apr 2013
    Location
    East Coast, South Carolina
    Posts
    717
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: counting the x;s and the ci's

    I think CI is just a mark not a column total number of non empty cells ie. marks and total number of X's 5/4
    There are 10 types of people in this world, those who understand binary, and those that dont.


    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,463
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    6 Thread(s)

    Default Re: counting the x;s and the ci's

    Try...

    =TEXT(COUNTIFS(A:A,"?*"),"000")&"/"&TEXT(COUNTIFS(A:A,"x"),"000")
    Assuming too much and qualifying too much are two faces of the same problem.

  6. #6
    Board Regular Flashbond's Avatar
    Join Date
    Mar 2010
    Location
    Turkey
    Posts
    653
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: counting the x;s and the ci's

    Quote Originally Posted by Drrellik View Post
    I think CI is just a mark not a column total number of non empty cells ie. marks and total number of X's 5/4
    Ahh now I've got it, she asked for the ratio. You are right @Drrellik

    Then if CI is a column header, maybe it would be better to start range from A2

    =COUNTA(A2:A5)&"/"&COUNTIF(A2:A5,"x")
    Last edited by Flashbond; Feb 21st, 2018 at 11:46 PM.

  7. #7
    Board Regular Drrellik's Avatar
    Join Date
    Apr 2013
    Location
    East Coast, South Carolina
    Posts
    717
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: counting the x;s and the ci's

    Quote Originally Posted by Aladin Akyurek View Post
    Try...

    =TEXT(COUNTIFS(A:A,"?*"),"000")&"/"&TEXT(COUNTIFS(A:A,"x"),"000")
    This is even better for the formatting 005/004 just make sure you don't have the formula in column A unless you change to define the range
    There are 10 types of people in this world, those who understand binary, and those that dont.


    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  8. #8
    New Member
    Join Date
    Feb 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: counting the x;s and the ci's

    U51 -u59

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,463
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    6 Thread(s)

    Default Re: counting the x;s and the ci's

    Quote Originally Posted by Mrs Lyttle View Post
    U51 -u59
    =TEXT(COUNTIFS(U51:U59,"?*"),"000")&"/"&TEXT(COUNTIFS(U51:U59,"x"),"000")

    This excludes the formula blanks and true numbers.
    Last edited by Aladin Akyurek; Feb 21st, 2018 at 11:53 PM.
    Assuming too much and qualifying too much are two faces of the same problem.

  10. #10
    New Member
    Join Date
    Feb 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: counting the x;s and the ci's

    Thank You so very much.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •