Click here for important information about Computing.net.

Okay, this should I think be fairly simple, but I would like to be use excel to look at a column of data and tell me how many times each value occurs. So in a column of say names Fred appears 7 times and Bob appears 9 times, it would display the total.

You can use countIF, but you would have to tell it what to count each time, is there a way to highlight a whole column and to get Excel to analyse the values and tell how many times each one occurs.

Two options come to mind: 1 - Manually create an Advanced Filter list of Unique values from the long list and then drag a COUNTIF Formula down along side it.

Data...Filter...Advanced Filter...fill in the dialog box.

2 - Use VBA to do #1 for you:

Assuming your long list in Column A, this will put the Filtered List in Column B and the Counts in Column C,

Option Explicit Sub CountWithoutLooking() Dim lstA_Rw, lstB_Rw, nxt_Rw As Integer 'Determine Last Row in Column A lstA_Rw = Range("A" & Rows.Count).End(xlUp).Row 'Filter the list into Column B Range("A1:A" & lstA_Rw).AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Range("B1"), Unique:=True 'Determine Last Row in Filtered List lstB_Rw = Range("B" & Rows.Count).End(xlUp).Row 'Put Formulas next to Filtered List Range("C1") = "Counts" With Range("C2:C" & lstB_Rw) .Formula = "=COUNTIF($A$2:$A$" & lstA_Rw & ",B2)" End With End Sub

Hi, If your column of say names is in column C in cells C1 to C100

Put the name to find in cell A1 and

this formula in Cell B1:=COUNTIF(C1:C100,A1)

The count will be shown in B1.If you know the list of names that may appear in column C (in this example) then create a list of those names.

If the list is in column G, Cells G1 to G10, then use data validation in Cell A1, so that you can select from a list when you click on Cell A1.

Select Cell A1 and (in Excel 2003) select Data - Validation...

In the dialog box select List from the 'Allow:' drop-down and in the 'Source:' box enter the range of cells containing the list of possible names (e.g., =$G$1:$G$10).Now A1 has a drop-down to select from and B1 will display the number of occurrences for the selected 'name'

This assumes that the 'names' in column C are just 'names' and not 'names' embedded in longer texts.

Regards

Thanks guys, very helpful :-)

Ask Your Question

Weekly Poll

Do you think Intel's new hybrid chips will better compete with Apple/ARM?

Yes (14) | ||

No (14) | ||

I don't know (15) |

Discuss in The Lounge

Poll History