Count One Column If Another Column Meets Criteria in Excel

Are you trying to count values from one column if the other column meets the criteria? There are numerous ways that you can follow. This article will discuss how to calculate one column if another column meets the criteria in Excel in six different ways.

Contents hide

📁 Download Excel File

Learn to Count One Column If Another Column Meets Criteria with 6 Unique Approaches

1. Using COUNTIF Function

2. Applying COUNTIFS Function

3. Using Pivot Table

4. Combination of SUMPRODUCT and COUNTIF Functions

5. Employing COUNTIF Function in VBA

6. Applying COUNTIFS Function in VBA

📝 Takeaway from This Article

Conclusion

📁 Download Excel File

Download the Excel file we used to create this article so you can practice.

Count One Column If Another Column Meets Criteria.xlsx

Learn to Count One Column If Another Column Meets Criteria with 6 Unique Approaches

The six methods for counting one column while using other criteria are illustrated here.

We have used the dataset below to make it easy for you to understand. The four columns of the given data set contain information about Apple Corporation’s selling statistics, such as the areas, months, product types, and total sales.

APPROACH

1. Using COUNTIF Function

The COUNTIF function in Excel counts the number of cells for a criterion. For example, follow the steps if you want to Count One Column and If Another Column Meets the Criteria in Excel using the COUNTIF function.

⬇️⬇️ STEPS ⬇️⬇️

First, specify the criteria. Here, we want to know how many areas of sales revenue came from selling MacBooks. So, we set the criteria MackBook in cell H5.

Then, specify a cell where you want to see your result and type the function COUNTIF, don’t forget to start with an equal “=” sign.

After that, specify the range of the criteria. Here, as we have used MacBook as criteria, we have selected products range cell D6:D17.

Then, put a comma, insert the criterion cell number, and close the parenthesis. Here, in this case, it is cell H5.

You can copy the formula used here from below.

=COUNTIF(D6:D17,H5)

Finally, press Enter. Therefore, you will be able to see the result.

Read More: 2 Ways to Count Columns for VLOOKUP in Excel

Approach

2. Applying COUNTIFS Function

The COUNTIFS function is quite similar to the COUNTIF function, except that the COUNTIFS function can be used for multiple ranges and criteria. Want to know how to do this? Just follow some simple steps.

Here we intend to know how many areas Apple Watch sold in January. So, both Apple Watch and January are our criteria.

⬇️⬇️ STEPS ⬇️⬇️

In this method, you can select two criteria simultaneously. So, insert those criteria in two different cells. For example, we selected cells H5 and H6 for our first and second criteria.

Then, specify a cell where you will be seeing your answer. For example, we selected cell H8 here.
After that, now it’s to insert the function COUNTIFS in your specified cell. Again, don’t forget to put an equal sign in the beginning.

Now, specify the first range for the first criteria. Here, we have selected the cells D6 to D17 for criteria one’s range.
Then, select the first criteria cell; here, it is H5.
Then again, specify the second range for the second criterion. Here, we have selected the cells C6 to C17 for criteria two’s range.
Finally, select the 2nd criteria cell; here, it is H6.

The function used in the selected cell is given here

=COUNTIFS(D6:D17,H5,C6:C17,H6)

Then press Enter, and you will be able to see the answer.

🔨 Formula Breakdown

COUNTIFS(D6:D17,H5,C6:C17,H6)

First, the COUNTIFS function applies the first criteria, H5, across the cell range D6:D17 and counts the number of times the criteria were met.
After finding the number of cells which meet the first criteria, the COUNTIFS function starts to count for the second criteria H6 in the second criteria’s assigned range, which is C6:C17, and returns the value.

Approach

3. Using Pivot Table

You may be familiar with the Excel Table function, and Pivot Table is a useful feature for handling a large amount of data. Don’t worry if you are unfamiliar with this; here are some instructions you can follow to use Pivot Table while counting columns if it meets the criteria.

Suppose we want to know how many products were sold for all months.

⬇️⬇️ STEPS ⬇️⬇️

Follow the serial to create a Pivot Table; select the data ➝click Insert ➝ select the Pivot Table option ➝ click on the From Table/ Range.

A dialogue box will appear. Select the New worksheet option if you want to create the table in a new worksheet. Otherwise, you can select Existing Worksheet if you want it in the same worksheet.
Press OK. Your Pivot Table will be created.

A dialogue box will be opened in a New Worksheet.

First, click on the box which you want to count for. Here, we wanted to know how many products were sold in the given Months. So, we have clicked months first. Or you can drag the months to the Row box below.

Then, we have to click on what we want to count; as mentioned earlier, we want the product numbers. So, we clicked on the Products box.

Now, a Pivot Table will be created. And in the last row, you can see the Grand Total, which is the total number you wanted to count.

Approach

4. Combination of SUMPRODUCT and COUNTIF Functions

The SUMPRODUCT Function of Excel can also be used to count one column if another column meets the criteria. Here, we will evaluate the No. of Areas where the Apple watch was sold, regardless of the month. So we’ve followed the following steps below:

⬇️⬇️ STEPS ⬇️⬇️

First, the criteria that we want to fix are entered in a specific cell. Here we have entered the Apple Watch, which is our criteria in cell H5.
Now, enter the SUMPRODUCT function in a cell where you want to see your answer. Here, we have selected cell H6 and typed the function SUMPRODUCT.

Now, starting with new parentheses “(“select the range of the criteria. In this case, it is D6 to D17.

Use the equal “=” sign; now select the cell where criteria are assigned. So, here we selected the criteria for cell
Close the parentheses “)” and enter a division “/” sign.

Enter the function COUNTIF followed by parentheses.
Select the entire range that you want to count. That’s why we selected the entire Area column, which is B6:B17.
Finally, insert a comma “,” and again select the cell you want to count.
Closing the function with parentheses, now press ENTER.

You can copy the function written in the cell from the following

=SUMPRODUCT((D5:D17=H5)/COUNTIF(B6:B17,B6:B17))

🔨 Formula Breakdown

SUMPRODUCT((D5:D17=H5)/COUNTIF(B6:B17,B6:B17))

The (D6:D17=H5) part firstly counts the cells in this D6 to D17 cells for the criteria H5 and assigns the count 4 to the function.
The COUNTIF function counts across the cells B6:B17 for the criteria B6:B17, which we want to count and assign the value 1 in SUMPRODUCT.
Finally, the SUMPRODUCT function executes the division among the assigned values for the division “/” sign and returns the value 4.

Approach

5. Employing COUNTIF Function in VBA

You may be familiar with Excel VBA (Visual Basic for Application). You can write some VBA code for the COUNTIF function to count one column if another column meets the criteria in Excel. Just follow the steps below. Here we wanted to know the number of products sold in the USA in January.

⬇️⬇️ STEPS ⬇️⬇️

First, click the Developer tab option from the ribbon. And from there, click the Visual Basic option to open.

(Please note that the Developer option may not be available in your ribbon, in this case, you have to go to the option and, from there, select the developer from view.)

After opening the Microsoft Visual Basic for Applications, click the Insert and click Module.

Insert the following code in the module opened.

Sub CountIf_with_Criteria()
Dim gResult As Double
gResult = Application.WorksheetFunction.CountIf(Range("C6:C17"), "January")
MsgBox "Total number of Products Sold in January: " & gResult

Now from the Developer’s code section, select the Macros.

A dialogue box will open; select the option Run to run the code.

Now you will see the final result in a different dialogue box.

Approach

6. Applying COUNTIFS Function in VBA

For the COUNTIFS function, you can also write a VBA code. Here are the steps you will be doing. Just like the function COUNTIFS, this also can count for two criteria.

⬇️⬇️ STEPS ⬇️⬇️

First, click the Developer option from the ribbon. And from there, click the Visual Basic option to open.

After opening the Microsoft Visual Basic for Applications, click the Insert and click the Module option to open.

In this new Module, you have to write the code given below

Sub CountIfs_with_Criteria()
Dim kResult As Double
kResult = Application.WorksheetFunction.CountIf(Range("C6:C17"), "January")
MsgBox "Total number of Proucts Sold in January : " & kResult
End Sub

Now from the Developer’s code section, select the option to select the Macros.

A dialogue box will open; select the option Run to run the code.

Now you will see the final result in a different dialogue box.

📝 Takeaway from This Article

📌 You can now count any column if another one/ two column meets the criteria.

📌 Learn one use of the Pivot Table.

📌 Can use COUNTIF and SUMPRODUCT functions if only one column meets the criteria.

📌 You can also use the COUNTIFS function for counting for two criteria.

📌 Count one column using VBA codes.

Conclusion

Throughout the article, we have explained how to count one column if another column meets the criteria with steps. While managing an ample amount of data, these methods may be very useful. Please comment if you have any suggestions or questions. Don’t forget to visit our Excelden page to enhance your Excel-related knowledge.