Using Countif on a variable list

Lets say that the range over which you want counts of items starts in A4.

In B1 enter: =ADDRESS(ROW(A4),COLUMN(A4))&":"&ADDRESS(COUNTA(A:A),COLUMN(A4))

This formula keeps track of the range even when adds/removes occur.

Array-enter (that is, hit CONTROL+SHIFT+ENTER at the same time) the following formula in C1:

=SUM(1*(INDIRECT($B$1)="JOE"))

Aladin

Thanks Aladin. I think Iunderstand how that works ok, but don't I still need to be able to hardcode in the actual values for that? Because the contents of the list are completely variable, I can't pre-format the formula with the value, so I need to be make it scan through the list first to see what values are there. Hope I explained that alright.

If you don't know what values are there, then I'd suggest a different scheme. I'll come back to that later... now I have to run...

Aladin

Hi Tony

There are 2 ways I would do this:

1: Use a very simple Pivot Table on the single Column (the best way)

2: Use this macro. Just be sure Column B and C contain no information you want. Before you run it just select your data.

to put the macro in push Alt+F11 then paste in this code:

Sub CountOfEachItem()
Dim ListRange As Range
Dim NewList As Range
Set ListRange = Selection
Columns("B:C").Clear

ListRange.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"B1"), Unique:=True

Set NewList = Columns(2).SpecialCells(xlConstants)
NewList.Offset(0, 1).FormulaR1C1 _
= "=COUNTIF(" & ListRange.Address(ReferenceStyle:=xlR1C1) & " C[-2],RC[-1])"

Set NewList = Nothing
Set ListRange = Nothing
End Sub

Push Alt+Q to return to Excel
Push Alt+F8 and highlight "CountOfEachItem"
Click "Options" and asign a shortcut key.
Click OK then OK again
Select Your data and push your shortcut key.

Dave

OzGrid Business Applications

Thanks Aladin. Any assistance would be greatly appreciated

Tried option 2 Dave and it worked like a dream. Thanks a million. You're some man for one man