There are situations in Excel where you would want to count values in one column based on criteria in another column or columns. For example, you may want to find out from your dataset how many manufacturers of different products are based in certain regions of the country.
Some of the ways of counting values in one column based on criteria in another column(s) are:
- Employ PivotTable
- Apply the COUNTIF function
- Make use of the COUNTIFS function
- Use the SUMPRODUCT function
In this tutorial, we explore these ways and demonstrate exactly how they can be used in different situations.
1. Employ the PivotTable
A PivotTable can be used to summarise data and show the count of values in a column based on criteria in another column(s).
We create the PivotTable by doing the following:
Step 1 – Select the dataset you want to work with. In the example below it is range $A$1:$D$9.
Step 2 – On the Excel Ribbon, click the Insert tab.
Step 3 – Click on the PivotTable command button.
Step 4 – Ensure that Select a table or range option is selected in the Create PivotTable dialog box and that the right data range is selected.
Step 5 – Select the Existing Worksheet option in choosing where you want the PivotTable to be placed. Click in the Location box and select an empty cell.
Step 6 – Press OK.
After pressing OK, the PivotTable fields will appear on the right of the Excel window.
In our example, to find out the number of manufacturers who make the different products, drag the Manufacturer field into the VALUES box below and the Products field into the ROWS box.
We will get a PivotTable showing the number of manufacturers that produce different products as shown below:
If we want to find out how many manufacturers are located in each region, we need to untick the Product field and select the Region field. The PivotTable will adjust accordingly to show the count of manufacturers in the different regions as shown below:
2. Apply the COUNTIF function
We can use the COUNTIF function to count values in one column that meet the criteria in another column.
In the following example, we want to find out how many manufacturers make printers.
To get how many manufacturers make printers, we enter the following formula =COUNTIF(C2:C9,F3) in cell F6.
The data range C2:C9 is where the counting takes place. Cell F9 contains the criteria that is applied, which in this case is Printer.
The correct count of 2 manufacturers is returned as shown below:
See Also: Excel COUNTIF Partial Match (With Examples)
3. Make use of the COUNTIFS function
We make use of the COUNTIFS when we want to get the count of values in a column based on multiple criteria.
Suppose we want to find out how many computer manufacturers are based in Kisumu city, based on the following dataset.
We will need to enter the formula =COUNTIFS(B2:B9,F3,C2:C9,F6) in cell F9. When we press the Enter key, we will get the correct count of 2 computer manufacturers based in Kisumu city as shown below:
4. Use the SUMPRODUCT FUNCTION
We can also use the SUMPRODUCT function to count values in one column that meet the criteria in another column.
For example, we may want to find out how many manufacturers make printers based on the following dataset:
To get the correct count we need to input the formula =SUMPRODUCT((C2:C9=F3)/COUNTIFS(A2:A9,A2:A9)) in cell F6.
The data range A2:A9 is where the counting takes place. Range C2:C9 is the criteria range. In this case, the criteria is Printer, and it is referred to in cell F3.
When we press ENTER, we will get the correct count of 2 printer manufacturers as shown below:
Conclusion
In this tutorial, we have looked at different formulas to count values in a column that meet conditions or criteria in another column(s). You can use one or all of the ways depending on your situation and use case in Excel.
