Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I need some help with a countif function, which is illustrated by the attached file.

    Column A has a letter (S, F or O) and columns B to N contain numbers.

    I need a function to count the number of instances of S's, the number of F's and the number of O's within the full data range (B1 to N31 in the attached example).

    If a number appears in a row which starts with S, it will count as one. So there are 3 S's in Row 1, and a further 5 S's in Row 6 etc.

    I am looking for 3 separate functions (one for each designation letter), so in one cell I will have a function which tells me how many S's there are in the full data range, and in another cell, a different function which tells me how many F's etc.

    Many thanks

    Neil
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Does the attached file which uses a combination of COUNT and SUMIF help?
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you G

    Yes, it defininitely helps, and it gives exactly the result I am seeking.

    However, the adding of the extra column is a bit problematic to me (the extract I gave you is part of a much bigger worksheet).

    Is there a way to achieve this without adding a column? (The other bit you added at the bottom, with a cell with the appropriate letter, next to the cell with the function, doesn't cause me any problems).

    Many thanks.

    Neil

  4. #4
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post
    I have attached a file showing another method, using an array formula. This will not use an extra column, but if you have never used array forumlas before read the article I linked to.
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by neil View Post
    Thank you G

    Yes, it defininitely helps, and it gives exactly the result I am seeking.

    However, the adding of the extra column is a bit problematic to me (the extract I gave you is part of a much bigger worksheet).

    Is there a way to achieve this without adding a column? (The other bit you added at the bottom, with a cell with the appropriate letter, next to the cell with the function, doesn't cause me any problems).

    Many thanks.

    Neil
    The COUNT column can go wherever it isn't in the way, so you could put it on a separate Worksheet if required, or insert a new column between A and B, and count the cells there.

  6. #6
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Many thanks Tony (and again to G).

    That does exactly what I wanted. And thanks for the link to the article on arrays too. I haven't used them before, so I will make sure I read it.

    Cheers everyone for your interest!

    Neil

Posting Permissions

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