PDA

View Full Version : Count occurrences in specific column



clhare
02-27-2009, 06:14 AM
I have a spreadsheet where column 3 is used to indicate which specific category applies to each row. I need a macro that gives me a breakdown of how many times each category occurs in the spreadsheet.

Does anyone have a macro that can do that? Could the results be displayed in a table format on a new sheet?

IkEcht
02-27-2009, 06:22 AM
Are there standard-categories? Or should it be flexible enough to take new categories into account as well?

clhare
02-27-2009, 06:26 AM
I do have specific categories, but new ones could be added. If it's easier, I could always update the macro to add the new ones.

mikerickson
02-27-2009, 07:15 AM
Would =COUNTIF(3:3:,"category1") do what you want?

Bob Phillips
02-27-2009, 07:21 AM
G1: =C1
G2: =IF(ISERROR(MATCH(0,COUNTIF(G$1:G1,$C$1:$C$20&""),0)),"",
INDEX(IF(ISBLANK($C$1:$C$20),"",$C$1:$C$20),MATCH(0,COUNTIF(G$1:G1,$C$1:$C$20&""),0)))

G2 is an array formula, copy it down as many as you might need

H1: =IF(G1<>"",COUNTIF(C:C,G1),"")

Copy H1 down.

clhare
02-27-2009, 07:53 AM
Can you give an example using three categories ("Cat 1 (US)", "Cat 2 (LS)", "Cat 3 (GUR)")? I am not sure I understand how I would copy the above for each category (sorry, I am very new to Excel, let alone Excel VBA).

Thanks!

Bob Phillips
02-27-2009, 09:24 AM
Nobody has given you VBA, it is all standard Excel.

Do you mean trhat Column 3 will only contain those 3 values?

clhare
02-27-2009, 09:35 AM
No, there are about 12 different values that can be in this column. I want to get the number of times each value is used in the sheet.

Bob Phillips
02-27-2009, 09:52 AM
So my formulae should work.

clhare
02-27-2009, 10:25 AM
I don't understand how to use your formula. Where do I put it in order to use it?