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

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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%5B/url%5D ). 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.

Reply | Quote

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:

	A	B	C	D	E
1	Month	Region	A2:A10=”Jan”	B2:B10=”North”	(A2:A10=”Jan”)*(B2:B10=”North”)
2	Jan	North	TRUE	TRUE	1
3	Jan	West	TRUE	FALSE	0
4	Jan	South	TRUE	FALSE	0
5	Feb	East	FALSE	FALSE	0
6	Feb	North	FALSE	TRUE	0
7	Feb	West	FALSE	FALSE	0
8	Mar	South	FALSE	FALSE	0
9	Mar	East	FALSE	FALSE	0
10	Mar	North	FALSE	TRUE	0
11

Reply | Quote

Thank you Hans, perfect explanation. I understand now.

Reply | Quote

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

Reply | Quote

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…

Reply | Quote

Ah yes, that’s much simpler!

Reply | Quote