Excel Help (Countif sans specifics)

[oHUTCHYo]

My Google-fu is failing here as am struggling to find any similar requests...

I have a large quotation database, one of the columns is "Application", as our product has a countless list if potential customer applications. Therefore having a pre-defined pull down is pointless and only trying to pigeon hole which will ultimately miss new potential sales opportunities for unheard applications.

What I would like, put simply, is a count formula to go through this entire column and list most to least frequent entries of all applications. A normal countif function obviously relies on specific search criteria - which in this situation is not possible.

I know I could potentially use a filter and remove duplicate entries, but I was rather hoping to automate this and present it in a real-time summary.

Ideas?

 

[div0]

My guess is that you want to approach this in two steps.

/edit - just re-read your post and I don't think the below will help, but I'll leave it there just in case....



If I understand correctly you have a column (Application) which contains many entries, often duplicated. What you're hoping to achieve is a list of the unique entries, with a count of how many times each of them occurs (in the original column).

First I would get your list of unique entries - http://www.techrepublic.com/blog/mi...-a-list-of-unique-entries-in-an-excel-column/

Click on the column letter to select the entire column that contains your data and then copy it by pressing Ctrl + C, going to Edit | Copy, or clicking the Copy button on the Standard toolbar. (Select the whole column because you'll need the column header.)
Paste that data into a column away from your source data range or in a new sheet. After you paste the data, it will still be selected. However, if you inadvertently deselect it, just make sure the cell pointer is located anywhere in the data you pasted before you proceed. Note: You don't have to select all the data or sort it first for this tip to work.
Go to Data | Filter | Advanced Filter.By default, Excel will suggest filtering the list "in-place." There's nothing wrong with that, but I recommend copying the unique records to another location, so you can compare the two lists side by side.
Select the Copy To Another Location option (Figure B), select the Unique Records Only check box, and type B1 in the Copy To field.
Click OK, and Excel will copy the unique entries from the source column into the new location. It will even sort those entries in alphabetical order, as shown in Figure C.

If your original data is Column A and your 'unique' data is now Column B, then I would add another column C, which is a CountIf : =COUNTIF(A:A,B1).

 

[div0]

After re-reading your post, it sounds like you need a more automated way of generating the unique list (ie it might change as new items get added)?

If that's the case, I suspect you're best looking into a VBA function which generates the unique list, then just having a simple countif of the original data, matching to the unique list.

/edit - an attempt to avoid using VBA:

A1: 'Data'
B1: 'Unique'
C1: 'Count'

A2:A999 <your list of applications>
B2: =INDEX($A$2:$A$999,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$999),0,0),0))
C2: =COUNTIF(A:A, B2)

I think that should do what you want.

Obviously the formula in B2 is limited to a range of 999 (A2:A999), but you can extend this yourself if you know how long your data set is.

 

[Royal Fleet]

Would you not be able to achieve this with a Pivot table?

 

[oHUTCHYo]

div0, you're a true gem as always mate, thank you

Royal Fleet, thanks for the suggestion, that would have been the fall back if I couldnt do it via formula