This post will guide you how to countif multiple criteria on the same column in excel. How do I countif multiple criteria on the different columns in excel. How to use COUNTIF to count cells that match multiple criteria on the same column or the different columns in Excel.

1. Counting with Multiple Criteria in Same column using COUNTIF Function

Assuming that you have a list of data in column A and you want to count the number of two specified text string (excel and word) in range A1:A5, how to achieve it.

To count the number based on multiple criteria in the same range or column in Excel, you can create a formula based on the SUMPRODUCT function and the COUNTIF function. Just like this:

=SUMPRODUCT(COUNTIF(A1:A5,{"excel";"word"}))

Then type this formula into a blank cell and then press Enter key in your keyboard to apply this formula.

You will see that the count number is displayed in that cell.

And if you want to add one more criteria in this formula, for example, you need to count the number of three text strings in the given range, you just add that text string into the formula as below:

=SUMPRODUCT(COUNTIF(A1:A5,{"excel";"word";"access"}))

Or you can also use another formula based on the SUM function and the COUNTIFS function to achieve the same result. Like this:

=SUM(COUNTIFS(A1:A5,{"excel","word"}))

Type this formula into a blank cell, and then press Enter key.

2. Counting with Multiple Criteria in Same column using COUNTIFS Function

Assuming that you have two lists of data in Range A1:B5, and one is the product name and another is sale values. And you want to count the number of product “excel” and its sale value is greater 30. How to achieve it. You can use the COUNTIFS function to create a formula to achieve the result.

Like this:

=COUNTIFS(A1:A5,"excel",B1:B5,">30")

Type this formula into a blank cell and then press Enter key.

3. Counting with Multiple Criteria in Same column using VBA Code

We’ll explore using VBA code to achieve the same goal. This method provides more flexibility and customization options for counting with multiple criteria in Excel.

Press ‘Alt + F11‘ to open the Visual Basic for Applications (VBA) editor.

Right-click on any item in the project explorer, hover over “Insert” and select “Module” to add a new module.

In the newly created module, copy and paste the following VBA code:

Function CountMultipleCriteria(rng As Range, criteriaArray As Variant) As Long
    Dim cell As Range
    Dim count As Long
    Dim criteria As Variant
    
    For Each criteria In criteriaArray
        For Each cell In rng
            If cell.Value = criteria Then
                count = count + 1
            End If
        Next cell
    Next criteria
    
    CountMultipleCriteria = count
End Function

Close the VBA editor and return to your Excel workbook.

In a cell where you want the result, enter the formula:

=CountMultipleCriteria(A1:A5,{"excel";"word"})

Replace “A1:A5” with the range where you want to count, and “criteria1” and “criteria2” with your specific criteria.

After entering the formula, press Enter to execute it.

The VBA function will count occurrences of multiple criteria within the same column and display the result.

4. Video: Counting with Multiple Criteria in Same column

This Excel video tutorial on counting with multiple criteria within the same column. we’ll delve into three methods: two formula-based approaches utilizing SUMPRODUCT, COUNTIF, and COUNTIFS functions, along with a VBA code method.

5. Related Functions

• Excel COUNTIF function
  The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
• Excel SUMPRODUCT function
  The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
• Excel SUM function
  The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
• Excel COUNTIFS function
  The Excel COUNTIFS function returns the count of cells in a range that meet one or more criteria. The syntax of the COUNTIFS function is as below:= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)…