You can COUNTIF to Compare two dataset with frequent repetition of items which counts the number of cells in the selected dataset which meets the given criteria.
COUNTIF Syntax
COUNTIF(range,criteria)
Range: Range is the area of a dataset on which function is to be applied.
Criteria: Conditions which helps in deciding which cell to be counted.
Let us understand using COUNTIF with multiple examples.
Comparing Dataset Using COUNTIF Without Logical Operations
Below given is the dataset of companies winning the award for the “Best Company of the year” from 2005-2018.
Column A consists of years and column B consists of the company names winning the award the respective year.
In column C we selected company names for which we want to find the number of times they won the award.
To know how many times each company won an award, we will use the COUNTIF function by applying the above-mentioned syntax.
Here, B2 to B15 is our range and C2 is our criteria. To find how many times Crompton Greaves wins during 2005-2018, we will select cell C2 as our criteria.
Results:
Similarly, to know the same for other companies you just need to select C2 and holding the + sign at the tip of the cell and drag it to below cells. It automatically copies the same formula to those cells.
Comparing Dataset Using COUNTIF With Logical Operations
You can use logical operations like smaller than, greater than and equal to (<,=,>) with COUNTIF when you want to count the cells meeting the given criteria.
This is the list of 20 listed companies with their Market Capitalization and profit for the year 2019.
Let us use the COUNTIF function to count the number of companies with a market capitalization greater than 50000 crores.
Enter the range B3:B22 and criteria as “>50000” (crores) and press enter. Similarly, we can create and apply various parameters.
Result:
Using COUNTIF To Compare Two Dataset
There are two ways to compare two dataset in excel using Countif formula:
- Using COUNTIF with Conditional Formating.
- Using Only COUNTIF.
In the first method, the common cells in the criteria column will be highlighted and in the second method will display only the repetitions of the given criteria.
Let us discuss one by one, both the methods.
Compare Two Dataset Using COUNTIF With Conditional Formating
Data of runs scored by players in two innings is given here. To find whether runs scored in second innings by a player matches to runs scored in the first innings by a player.
To highlight the same runs scored in the second innings column or vise versa, First innings column will be our range and every cell of the second column will be our criteria.
Select the cells of C column and click on conditional formatting on the ribbon. Click on “New Rule” option. A dialog box will appear.
Select the last option in the list that is “Use a formula to determine which cells to format”.
In the formula bar, enter the formula as COUNTIF($B2:$B$11,$C2). Click on “Format” button, select the desired color to fill and then click “OK”.
Result:
Compare Two Dataset Using Only COUNTIF
To follow the second method, go into an adjacent cell D3 and enter the COUNTIF formula as
COUNTIF($B$2:$B$11,$C$2).
Copy the formula and paste it below till D8. The result is as follows:
The number adjacent to 1 in second innings column has matched with first innings scores.
In the case of large datasets where it is difficult for identifying manually, you can use Filter Function to get the required data points.
To do so, select the dataset and go to data and select Filter function or alternatively press “Alt+A+T”.
Click on the dropdown buttons D column, deselect “0” from the option and press enter key.
Now this filtered data can be highlighted in the second column using fill color. Click again on Filter to remove filters and your huge data is analyzed.
Furthermore, click on the link below to download the sample data file:
To learn other function like MID, QUOTIENT and KURT Functions, please click on the name of the function.
Additionally, you can download other accounting templates like Petty Cash Book, Simple Cash Book, and Accounts Payable Excel Templates from here.
We thank our readers for liking, sharing and following us on different social media platforms.
If you have any queries please share in the comment section below. We will be more than happy to assist you.
Leave a Reply