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.

## 📁 Download Excel File

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

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

** Read More: ****2 Handy Methods to Count Columns Until a Value Is Reached in Excel**

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