Summarize Data with CountIf withExcel VBA

Task:

The task is to summarize data using the COUNTIF function, which counts the number of times a specific condition is met within a range of cells.

Explanation of the COUNTIF function:

• COUNTIF(range, criteria):
  • range: This is the group of cells where you want to apply the condition.
  • criteria: This is the condition you are checking for. It could be a number, text, or a logical expression.

For example, if you want to count how many times the value « Apple » appears in a list, the formula would look like:

=COUNTIF(A2:A10, « Apple »)

In VBA, we can automate this process for multiple conditions or ranges.

Objective:

We will create a VBA code that summarizes a dataset by counting the number of occurrences of various items or values in a specified column, then displaying those counts in another column.

Step-by-Step VBA Code

Sub SummarizeDataWithCountIf()
    ' Declare necessary variables
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim summaryRange As Range
    Dim criteriaRange As Range
    Dim lastRow As Long
    Dim summaryRow As Long
    Dim criteria As String
    Dim countResult As Long
    ' Set the worksheet where your data is located (you can change this as per your sheet name)
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Find the last row in the data column (assuming data is in Column A)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row   
    ' Define the data range (from A2 to last data row in column A)
    Set dataRange = ws.Range("A2:A" & lastRow)   
    ' Define the summary range (this will be where we output the results, starting from column C)
    Set summaryRange = ws.Range("C2:C" & lastRow)   
    ' Clear any existing summary results in the summary range (column C)
    summaryRange.ClearContents   
    ' Set the initial summary row
    summaryRow = 2   
    ' Loop through each unique value in the data range
    Dim cell As Range
    Dim uniqueValues As Collection
    Set uniqueValues = New Collection   
    On Error Resume Next ' This will allow us to skip errors when adding duplicate values
    ' Add unique values from the data range to the collection
    For Each cell In dataRange
        uniqueValues.Add cell.Value, CStr(cell.Value) ' Using CStr to ensure uniqueness as key
    Next cell
    On Error GoTo 0 ' Turn off error ignoring   
    ' Loop through the unique values and calculate the count for each
    For Each criteria In uniqueValues
        ' Count the occurrences of the current unique value in the data range
        countResult = Application.WorksheetFunction.CountIf(dataRange, criteria)       
        ' Output the results to the summary range (column C for values, column D for counts)
        ws.Cells(summaryRow, 3).Value = criteria ' Output unique value in column C
        ws.Cells(summaryRow, 4).Value = countResult ' Output count result in column D      
        ' Move to the next summary row
        summaryRow = summaryRow + 1
    Next criteria
End Sub

Detailed Explanation:

1. Declare Variables:
   • ws: Refers to the worksheet where the data is located (here, it’s set to « Sheet1 »).
   • dataRange: This is the range where the data to be summarized is stored (in this case, column A, starting from A2 to the last row).
   • summaryRange: This is where we will output the unique values and their corresponding counts. The results will be shown starting from column C and column D.
   • criteriaRange: We don’t need a separate criteria range because we’re directly working with the unique values in the dataRange.
   • lastRow: This helps to dynamically determine the last row in the data column, so the script works for datasets of varying lengths.
   • summaryRow: This keeps track of where to output the next result in the summary section.
2. Identify the Last Row:
   • The line lastRow = ws.Cells(ws.Rows.Count, « A »).End(xlUp).Row identifies the last row of data in column A, ensuring that the data range is correctly captured.
3. Define the Data Range:
   • We define the dataRange from A2 to the last data row in column A. This is where the original data is located.
4. Prepare Summary Range:
   • We specify the range where the summary will be displayed (from column C onward).
5. Clear Existing Summary Data:
   • Before populating new results, we clear any previous content in the summary range using summaryRange.ClearContents.
6. Capture Unique Values:
   • We use a Collection object to capture unique values from the data range. Collections automatically discard duplicates, so this helps us avoid manually checking for uniqueness.
7. Count Occurrences:
   • For each unique value, we use the COUNTIF function in VBA (Application.WorksheetFunction.CountIf) to count how many times that value appears in the dataRange.
8. Output the Results:
   • For each unique value, we output the value in column C and the corresponding count in column D. The summaryRow variable keeps track of the current row for the output.
9. Repeat for All Unique Values:
   • This process continues for all unique values in the data range, populating the summary in columns C and D.

How to Run the Code:

1. Press Alt + F11 to open the VBA editor in Excel.
2. Go to Insert > Module to create a new module.
3. Paste the code into the module.
4. Press F5 or go to Run > Run Sub/UserForm to execute the code.

Example:

Assume you have the following data in column A:

After running the macro, the summary will be displayed like this:

The macro has counted how many times each item appears in column A and displayed the result in columns C and D.

Conclusion:

This VBA code efficiently summarizes data by counting the occurrences of each unique value in a range, using the COUNTIF function. It works dynamically, adjusting to the size of the dataset, and outputs the summary in a new section of the worksheet.