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