There are situations in Excel where you may want to count values in one column according to criteria in one or more other columns. For example, you may want to know from your dataset how many manufacturers of different products are based in certain regions of the country. Here are some of the ways you can count values in one column against criteria in one or more other columns:
- Using pivot tables
- Apply the COUNTIF function
- Using the COUNTIF function
- Using the SUMPRODUCT function
In this tutorial, we explore these methods and demonstrate exactly how they can be used in different situations.
1. Using the pivot table
A pivot table can be used to summarize data and display the number of values in one column according to criteria in one or more other columns. We create the pivot table as follows: Step 1 – Select the dataset you wish to work with. In the example below, this is the range $A$1:$D$9.Step 2 – On the Excel ribbon, click on theInsert tab. Step 3 – Click on the PivotTable command button. Step 4 – Make sure that the Select table or range option is selected in the Create PivotTable dialog box, and that the correct data range is selected. Step 5 – Select the Existing spreadsheet option to choose the location for the pivot table. Click in the Location box and select an empty cell. Step 6 – Press OK.
After pressing OK, the pivot table fields appear on the right-hand side of the Excel window.
In our example, to find out how many manufacturers make the different products, drag the Manufacturer field into the VALUES box below and the Products field into the LINES box. We’ll get a pivot table showing the number of manufacturers who produce different products, as shown below:
If we want to know how many manufacturers are located in each region, we need to uncheck the Product field and select the Region field. The pivot table will adjust accordingly to show the number of manufacturers in the different regions, as shown below:
2. Apply function COUNTIF
The COUNTIF function is used to count the values in a column that meet the criteria of another column. In the following example, we want to know how many manufacturers produce printers.
To find out the number of printer manufacturers, 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 criterion applied, which in this case is Printer. The correct count of 2 manufacturers is returned as shown below:
See also : Excel COUNTIF (with examples)
We use the COUNTIFS function when we want to obtain the number of values in a column according to several criteria. Suppose we want to know how many computer manufacturers are based in the city of Kisumu, based on the following data set.
We need to enter the formula =COUNTIFS(B2:B9,F3,C2:C9,F6) in cell F9. When we press Enter, we obtain the correct number of 2 computer manufacturers based in the city of Kisumu, as shown below:
4. Using the SUMPRODUCT FUNCTION
We can also use the SUMPRODUCT function to count the values in one column that meet the criteria of another column. For example, we might want to know how many manufacturers produce printers based on the following data set:
To obtain the correct count, we need to enter the formula =SUMPRODUCT((C2:C9=F3)/COUNTIFS(A2:A9,A2:A9)) in cell F6.Data range A2:A9 is where the count takes place. Range C2:C9 is the criteria range. In this case, the criterion is Printer, and is shown in cell F3. When we press ENTER, we get the correct number of 2 printer manufacturers, as shown below:
Conclusion
In this tutorial, we’ve looked at different formulas for counting values in one column that meet conditions or criteria in one or more other columns. You can use any or all of the methods depending on your situation and use case in Excel.