6 Ways to Count One Column in Excel If Another Column Meets Criteria

Sometimes we need to count the entity of a specific column based on a criterion. For example, you have made a list of the top ten companies based on their total market cap. Now you want to count the number of total companies from a specific country. How can you do that? Well, you don’t need to worry about it. Because, this article, will show you exactly how to solve this problem. Here, you will learn to count one column If another column meets the criteria in Excel.

I will use the list of top ten companies by their total market cap as a dataset. It has 4 columns in total. They are Company, Country, Sector, and Market Cap.

I will use this list to show you count one column if another column meets certain criteria. For example, I will show you the count number of US companies within the list that deals with Technology.

Count a Column If Another Column Meets Criteria with COUNTIF Function

The COUNTIF function can count based on different criteria. Here, I will write a formula with the COUNTIF function. The formula will count the number of companies in the United States that include the top ten companies listed by their market cap.

Syntax

=COUNTIF(range, criteria)

Formula

=COUNTIF(B4:B13,C15)

Formula Explanation

According to the COUNTIF function syntax, B4:B13 is the range and C15 is the criteria argument. Cell C15 refers to the country “United States”. So, the COUNTIF function counts the number of times “United States” appears in the range B4:B13.

To count a column if another column meets a criteria, follow these steps below:

1. Select a cell.
2. Insert the formula: =COUNTIF(B4:B13,C15)
3. Press ENTER.

The formula returns 7. This means the total number of companies from the United States in the top ten company list by their market cap is 7.

Count a Column If Another Column Meets Criteria with COUNTIFS Function

The COUNTIFS function counts based on multiple criteria. When you have multiple criteria to consider, you cannot use the COUNTIF function. Thus, the COUNTIFS function comes into consideration.

Syntax

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

Formula

=COUNTIFS(B4:B13,C15,C4:C13,C16)

Formula Explanation

According to the COUNTIFS function syntax:

• B4:B13 is criteria_range1.
• C15 is criteria1.
• C4:C13 is criteria_range2.
• C16 is criteria2.

Here, I have two criteria to meet. I want to count the number of companies that meet the following criteria of company should be United States-based and service sector is Technology.
To do that, follow the steps below:

1. Click on a cell.
2. Insert the formula: =COUNTIFS(B4:B13,C15,C4:C13,C16)
3. Press ENTER.

The formula returns 4. This means the number of companies in the list that are US-based and serve in the Technology sector is 4.

The COUNTIFS function first counts the number of times the United States appears in the range B4:B13. Then it again counts the number of times Technology appears in the range C4:C13. Finally, it returns the count number of Technology whose corresponding column is the United States.

•  3 Ways to Find Column Index Number in Excel
• 2 Ways to Count Columns in Excel for Vlookup

Count Column If Another Column Meets Criteria by SUMPRODUCT & COUNTIFS Functions

Previously, I used the COUNTIF function to count the number of companies in the list from the United States. Now I will use a combination of the SUMPRODUCT & COUNTIF functions to replace the COUNTIF function.

Syntax

=SUMPRODUCT((array1/COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…))

Formula

=SUMPRODUCT((B4:B13=C15)/COUNTIFS(A4:A13,A4:A13))

Formula Explanation

• B4:B13=C15: Here, C15 refers to the country United States. The range B4:B13 is the Country column. This array returns TRUE when the country name is the United States; otherwise, it returns FALSE. If you select the array, B4:B13=C15 and press the F9 key, it returns {TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE}.
• COUNTIFS(A4:A13,A4:A13): This part acts like an array formula. Here, the first range acts as a criteria_range and the second range acts as a criterion. The standalone output of this part is {1;1;1;1;1;1;1;1;1;1}.
• (B4:B13=C15)/COUNTIFS(A4:A13,A4:A13): The output of this part is {1;0;1;1;1;1;0;1;0;1}.
• SUMPRODUCT((B4:B13=C15)/COUNTIFS(A4:A13,A4:A13)): It is equivalent to SUMPRODUCT({1;0;1;1;1;1;0;1;0;1}). Here, the SUMPRODUCT function sums up all the values of the array {1;0;1;1;1;1;0;1;0;1}. Thus, the output becomes 7.

To use SUMPRODUCT & COUNTIFS Functions, follow these steps below:

1. Select a cell.
2. Insert the formula: =SUMPRODUCT((B4:B13=C15)/COUNTIFS(A4:A13,A4:A13))
3. Press ENTER.

The formula returns 7. This means the total number of companies from the United States in the top ten company list by their market cap is 7.

Count a Column If Another Column Meets Criteria Using Pivot Table

Pivot Table is a powerful tool to analyze, organize and present data in Excel. It has a feature that can auto-count the entities of a column based on criteria.

Now, I will use the Pivot Table to count the number of companies from each country listed in the top ten companies by market cap list. First, you need to convert your data table into Pivot Table.

So, go through the procedure to count the column if another column meets criteria using Pivot Table:

1. Select Insert tab > Tables group > PivotTable drop-down > From Table/Range command.
   A dialog box ‘PivotTable from table or range’ appears.
2. Insert your data table range in the Table/Range box.
3. Now, you need to choose where you want to place the Pivot Table. Two options are available, New Worksheet and Existing Worksheet.
   For this instance, I’m selecting the Existing Worksheet option.
4. You need to insert a cell address in the Location box.
5. After that, click OK.
   
   
6. In the PivotTable Fields dialog box, select the Company and Country column.
7. Keep the Country column in the Rows section.
8. Then, keep the Company column in the Values section. It will turn into Count of Company.

Hence, your Pivot Table is ready. Now, you can see the number of companies from each country in the Count of Company column.

• [2 Cases] Count Columns in Excel Until a Certain Value Reached
• Count Rows in Excel Until a Certain Value Reached [2 Cases]

Count Column If Another Column Meets Criteria with COUNTIF in VBA

Here, I will write a code using the CountIf function in VBA. The code will count the number of companies from the United States enlisted in the top ten companies by market cap list.

To count a column if another column meets criteria, use the VBA code by following these steps below:

1. First, open the Visual Basic Editor by pressing the ALT + F11 keys.
2. Then, create a new Module from the Insert tab.
   
   
3. Insert the following VBA code in the new Module.
   

   Sub CountIf_with_Criteria()
   
   Dim gResult As Double
   
   gResult = Application.WorksheetFunction.CountIf(Range("B4:B13"), "United States")
   MsgBox "Total number of companies from United States: " & gResult
   
   End Sub

   Code Description

   This code creates a subroutine called CountIf_with_Criteria. It counts the country name ‘United States’ in the range B4:B13. Then it displays the count number using a pop-up message box.

   Editable Lines

    gResult = Application.WorksheetFunction.CountIf(Range("B4:B13"), "United States")

   Here, update the range B4:B13 as per your requirement. Then replace ‘United States’ with whatever you want to count within your range.

   MsgBox "Total number of companies from United States: " & gResult

   You can update the message inside the double inverted comma to display whatever you want.

4. Go back to your worksheet and press ALT + F8 to open the Macro dialog box.
5. Select the subroutine CountIf_with_Criteria and hit Run.
   
   

A small dialog will appears with a message. The message is ‘Total number of companies from United States: 7’.

Count Column If Another Column Meets Criteria with COUNTIFS in VBA

Now, I will show you to handle multiple criteria using the COUNTIFS function in VBA code. To do that, go through these steps below:

1. First, open the Visual Basic Editor first by pressing the ALT + F11 keys.
2. Then, create a new Module from the Insert tab.
   
   
3. Insert the following VBA code in the new Module.
   

   Sub CountIfs_with_Criteria()
   
   Dim gResult As Double
   
   gResult = Application.WorksheetFunction.CountIfs(Range("B4:B13"), "United States", Range("C4:C13"), "Technology")
   MsgBox "Total number of tech companies from United States: " & gResult
   
   End Sub

   Code Description

   This code creates a subroutine called CountIfs_with_Criteria. It counts the number of companies in the United States under the Technology category. Then it displays the count number using a pop-up message box.

   Editable Lines

   gResult = Application.WorksheetFunction.CountIfs(Range("B4:B13"), "United States", Range("C4:C13"), "Technology")

   Here, B4:B13 is the first criteria range, and “United States” is the first criteria. Then C4:C13 is the second criteria range and “Technology” is the second criteria. You need to update both criteria range as well the criteria.

   MsgBox "Total number of tech companies from United States: " & gResult

   You can update the message inside the double inverted comma to display whatever you want.

4. Go back to your worksheet and press ALT + F8 to open the Macro dialog box.
5. Select the subroutine CountIfs_with_Criteria and hit Run.

A small dialog will appears with a message. The message is “Total number of tech companies from United States: 4“.

• 3 Methods to Convert Column Number to Letter in Excel
• [Excel VBA] Count Columns & Rows Having Data (10 Editable Codes)

Conclusion

So, I have discussed 6 different ways to count one column in Excel if another column meets the criteria. I expect you’ve found this article helpful. You can read more Excel articles from our website’s Blog page. Have a great workday!

Frequently Asked Questions

What is Countif with index match?

COUNTIF with INDEX–MATCH in Excel is a powerful combination of functions for advanced data analysis. The COUNTIF function counts cells meeting a specific condition, while INDEX–MATCH performs a flexible lookup. By integrating these functions, users can dynamically count occurrences based on criteria retrieved using INDEX–MATCH. This synergy allows for sophisticated conditional counting and lookup operations, enhancing the precision and adaptability of Excel formulas in handling complex data sets. Employing COUNTIF with INDEX–MATCH is particularly valuable for scenarios demanding both conditional counting and dynamic data retrieval, offering a comprehensive solution for advanced spreadsheet tasks.

What is the difference between COUNTIF and COUNTIFS?

COUNTIF and COUNTIFS are distinct Excel functions designed for conditional counting, but their usage varies. COUNTIF counts cells meeting a single specified condition, using the syntax =COUNTIF(range, criteria). In contrast, COUNTIFS is employed for counting based on multiple conditions simultaneously, utilizing the syntax =COUNTIFS(range1, criteria1, range2, criteria2, …). The primary difference lies in complexity: COUNTIF suits simple criteria, while COUNTIFS accommodates scenarios with multiple, intertwined conditions. Whether tallying based on a single condition or navigating intricate data scenarios, choosing between COUNTIF and COUNTIFS hinges on the level of criteria complexity involved.

How do I count if a cell meets criteria in Excel?

In Excel, use the COUNTIF function to count cells that meet specific criteria. The syntax is =COUNTIF(range, criteria). For instance, =COUNTIF(A1:A10, “>50”) counts the number of cells in range A1 to A10 that are greater than 50. Simply replace ‘range’ with your desired cell range and ‘criteria’ with the condition you want to apply. This versatile function is ideal for straightforward conditional counting in Excel, providing a quick and effective way to analyze data based on specified criteria.