Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count items in one column based on another column (97, XP)

    I have a spreadsheet in which I need to count items in column

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count items in one column based on another column (97, XP)

    You can use Data | Advanced Filter... to create a list of unique names. Then, you can use the worksheet function COUNTIF to count the number of times each name occurs. See attached sampe workbook.

  3. #3
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count items in one column based on another column (97, XP)

    Thanks for the example. I guess i was not as clear as I should have been about the codes. For each name, there will likely be seveal codes repeated and I need to be able to count how many of each code for each name. In other words, for Smith there might be 6 "SE 09's" and 4 "CI 03's", and so on.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count items in one column based on another column (97, XP)

    This kind of grouping and counting is child's play in Access. Any chance you can use that? You can import or link Excel tables in Access.

  5. #5
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count items in one column based on another column (97, XP)

    Regrettably. no. Access is not an option. I have found a way to do it although not very elegant by using a combination of COUNTIF and OFFSET. I just thought there must be a better method. Maybe even using an array of some type.

  6. #6
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count items in one column based on another column (97, XP)

    Okay, I found the answer. I don't understand why or how it works but it does work. if anyone can explain it, I would appreciate it. I found the answer on John Walkenbach's page (http://j-walk.com/ss/excel/tips/tip74.htm ). His tip said.. and I quote

    Count of Sales where Month="Jan" AND Region="North"
    For multiple criteria in different fields, the COUNTIF function doesn't work. However, you can use an array formula. When you enter this formula, use Ctrl+Shift+Enter:

    =SUM((A2:A10="Jan")*(B2:B10="North"))

    ------------
    I just substituted appropriate ranges and criteria.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count items in one column based on another column (97, XP)

    This type of formula uses the fact that in Excel formulas TRUE = 1 and FALSE = 0.
    A2:A10="Jan" evaluates to an array of 1 and 0 with 1 in the places where the cell value is equal to "Jan".
    B2:B10="North" evaluates to an array of 1 and 0 with 1 in the places where the cell value is equal to "North".
    If you multiply these, you get an array of 1 and 0. Since 0 * 0 = 0, 1 * 0 = 0, 0 * 1 = 0 and 1 * 1 = 1, this array will only have 1 where the value in column A is "Jan" AND the value in column B is "North".
    The SUM function adds the values in this array. The sum is equal to the number of values equal to 1, i.e. the number of times that the cell in column A is equal to "Jan" AND the cell in column B is equal to "North".

    Here is a table as illustration:

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><td align=center>1</td><td align=center>Month</td><td align=center>Region</td><td align=center>A2:A10="Jan"</td><td align=center>B2:B10="North"</td><td align=center>(A2:A10="Jan")*(B2:B10="North")</td><td align=center>2</td><td align=center>Jan</td><td align=center>North</td><td align=center>TRUE</td><td align=center>TRUE</td><td align=center>1</td><td align=center>3</td><td align=center>Jan</td><td align=center>West</td><td align=center>TRUE</td><td align=center>FALSE</td><td align=center>0</td><td align=center>4</td><td align=center>Jan</td><td align=center>South</td><td align=center>TRUE</td><td align=center>FALSE</td><td align=center>0</td><td align=center>5</td><td align=center>Feb</td><td align=center>East</td><td align=center>FALSE</td><td align=center>FALSE</td><td align=center>0</td><td align=center>6</td><td align=center>Feb</td><td align=center>North</td><td align=center>FALSE</td><td align=center>TRUE</td><td align=center>0</td><td align=center>7</td><td align=center>Feb</td><td align=center>West</td><td align=center>FALSE</td><td align=center>FALSE</td><td align=center>0</td><td align=center>8</td><td align=center>Mar</td><td align=center>South</td><td align=center>FALSE</td><td align=center>FALSE</td><td align=center>0</td><td align=center>9</td><td align=center>Mar</td><td align=center>East</td><td align=center>FALSE</td><td align=center>FALSE</td><td align=center>0</td><td align=center>10</td><td align=center>Mar</td><td align=center>North</td><td align=center>FALSE</td><td align=center>TRUE</td><td align=center>0</td><tr><td align=center>11</td><td align=right>

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count items in one column based on another column (97, XP)

    I may be way off mark here but couldnt you use a pivot table on the range that you specifiy with a count of the data you want in the middle?
    just a thought...

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count items in one column based on another column (97, XP)

    Ah yes, that's much simpler!

  10. #10
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count items in one column based on another column (97, XP)

    Thank you Hans, perfect explanation. I understand now.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count items in one column based on another column (97, XP)

    But if you have the time, investigate FatherJack's suggestion. A pivot table is a very elegant solution.

Posting Permissions

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