Results 1 to 11 of 11

20030518, 17:11 #1
 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

20030518, 22:12 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20030518, 23:20 #3
 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.

20030518, 23:24 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20030519, 00:11 #5
 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.

20030519, 01:56 #6
 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://jwalk.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.

20030519, 11:24 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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>

20030519, 13:31 #8
 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...

20030519, 13:45 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Count items in one column based on another column (97, XP)
Ah yes, that's much simpler!

20030519, 20:26 #10
 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.

20030519, 20:42 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.