MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Using Countif on a variable list

Posted by Tony on April 05, 2001 3:09 AM

This is probably a simple one but....

I have a list of values which I want to total. The problem is that the list can change from day to day and I need to run this daily. I know I can use countif when I know what values to look for, but what I want to do is scan through the list and extract one of each value first. What I want to get is the total count of each value.

For example,

Results: JOE 1

Posted by Aladin Akyurek on April 05, 2001 3:37 AM

Lets say that the range over which you want counts of items starts in 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:



Posted by Tony on April 05, 2001 4:16 AM

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.

Posted by Aladin Akyurek on April 05, 2001 4:28 AM

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...


Posted by Dave Hawley on April 05, 2001 4:29 AM

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

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.


OzGrid Business Applications

Posted by Tony on April 05, 2001 4:30 AM

Thanks Aladin. Any assistance would be greatly appreciated

Posted by Tony on April 05, 2001 4:41 AM

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