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

Table of Contents

### Introduction to the Dataset

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

## Easiest Way to Count a Column If Another Column Meets Criteria

The **COUNTIF** function can count based on different criteria. It’s super easy to use.

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)

**Usage Guide**

**Step_1:** Insert the formula in cell **C16**.

**Step_2:** Press **ENTER**.

**Final Result**

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

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

### Alternative Ways #1: Using 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.

Here, I have two criteria to meet. I want to count the number of companies that meet the following criteria.

- The company should be
**United States**-based. - Its service sector is
**Technology**.

I will write a formula with the **COUNTIFS** function to solve this problem.

**Syntax**

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

**Formula**

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

**Usage Guide**

**Step_1:** Insert the formula in cell **C17**.

**Step_2:** Press **ENTER**.

**Final Result**

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

**Formula Explanation**

According to the **COUNTIFS** function syntax,

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

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

### Alternative Ways #2: Using SUMPRODUCT & COUNTIFS Function

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

**Usage Guide**

**Step_1:** Insert the formula in cell **C16**.

**Step_2:** Press **ENTER**.

**Final Result**

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

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

**🔗 2 Ways to Count Columns in Excel for Vlookup**

### Alternative Ways #3: 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.

**Usage Guide**

First, you need to convert your data table into **Pivot** **Table**. For that,

**Step_1:** Select **Insert** tab **⋙** **Tables** group **⋙** **PivotTable** drop-down **⋙ From Table/Range** command.

A dialog box** ‘PivotTable from table or range’** appears.

**Step_2:** Insert your data table range in the **Table/Range** box.

**Step_3:** Now you need to choose where you want to place the **Pivot** **Table**.

Two options are available,

**New Worksheet****Existing Worksheet**

For this instance, I’m selecting the **Existing** **Worksheet** option.

**Step_4:** You need to insert a cell address in the **Location** box.

**Step_5:** After that, click **OK**.

**Step_6:** In the **PivotTable** **Fields** dialog box, select the **Company** and **Country** column.

**Step_7:** Keep the **Country** column in the **Rows** section.

**Step_8:** Then keep the **Company** column in the **Values** section. It will turn into **Count of Company**.

**Final Result**

So 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**

### Alternative Ways #4: Using COUNTIF Function 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 use the **VBA** code,

**Step_1:** First, open the **Visual Basic Editor** by pressing the **ALT + F11** keys.

**Step_2:** Then create a new **Module** from the **Insert** tab.

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

**Step_4:** Go back to your worksheet and press** ALT + F8** to open the **Macro** dialog box.

**Step_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 Rows in Excel Until a Certain Value Reached [2 Cases]**

### Alternative Ways #5: Using COUNTIFS Function in VBA

Now I will show you to handle multiple criteria using the **CountIfs** function in **VBA**.

**Usage Guide**

**Step_1:** First, open the **Visual Basic Editor** first by pressing the **ALT + F11** keys.

**Step_2:** Then create a new **Module** from the **Insert** tab.

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

**Step_4:** Go back to your worksheet and press **ALT + F8** to open the **Macro** dialog box.

**Step_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**

## 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!

**🔗 [Excel VBA] Count Columns & Rows Having Data (10 Editable Codes)**