Updated June 12, 2023
Part -14 – MS Excel COUNTIF Function
Download MS Excel COUNTIF Function Templates
MS Excel COUNTIF Function ( without solution)
MS Excel COUNTIF Function ( with solution)
Learn how to organize, format, and calculate data smoothly. Develop skills to master Excel tools, formulae, and functions. Analyze data from different perspectives.
Transcript Video For The MS Excel COUNTIF Function
MS Excel COUNTIF Function
In this set of a video tutorial, we are going to learn a couple of more formulas like MS Excel COUNTIF Function and Sum IF Function, and in which scenarios these are really useful. So specifically talking about her case study. Let’s look at the overall employees; there 19 in number, and they are divided into various departments marketing, IT, Finance, support and likewise, you know they come from different genders, male, female, and they have a different salary as such. Now let’s say if you want to answer these kinds of questions. What is the total salary of marketing professionals? Now if this is the question that we are kind of looking at, what we will have to do?
If we are doing it in a traditional way, would be to SUM total all the marketing employees’ salaries and do it mechanically. So that’s obviously an easy choice to do, but when you are dealing with large data, it will be really closed to impossible to do it mechanically. So in that way, you know, it will be very easy if you are using some functions within IF command. Likewise, you know, you remember if we had earlier used if function, we were doing the certain calculation with some kind of conditions, now what we need to really do here is to Sum total the salaries with the condition that only choose the marketing professional. So that’s how, you know, you can apply this set of formulas. So we will learn all these formulas now, so two functions which we will be dealing with now would be MS Excel COUNTIF Function and SUM IF Function. Now these two commands are really easy if you know how to apply IF functions. So Let me apply this in our case, so let’s write a couple of questions.
So let’s calculate the following total number of employees in each department, and we should also try to calculate the total salary in each department. So there are various kinds of departments. So one is, I should write it down marketing, IT, Finance, support, these are the 4 departments which we have in this table, and we can also try to calculate the salary drawn, and the total number of employees who are male, and how many of them are female. So what we will do is we will use ms excel count if function and SUM IF function to do the desired task. So let us now first and info most calculate the total no. of employees. For this, let us use count if function. So as we have seen for another set formula, we have to start with = to type count if bracket, open the movement you do that you can see the syntax that needs to be given for count IF. It asks for two things; one is the range, and the second one is criteria, so where is the range on which we need to find the total number of employees the range would be the marketing, IT, finance; this column would be the range.
So I have the taken for the top to the very bottom and what exactly is a criterion. The criteria is that select the marketing guys, so I can give criteria in two ways one is if I can type this manually by writing marketing and within Inverted Comma and bracket close so, what will happen is this Excel will provide me with the total number of marketing employees by using this count IF. So it’s only counting when the department is marketing; otherwise, it leaves it. So as I told you, there are two ways of doing it one, we can write this under Inverted Comma, or second, we can delete this, and we can directly choose the one which we have written in this cell which is cell C30 that is the criteria. Don’t use
So we enter it, and we find that answers are relatively same. Likewise, let us now find out what is the total salary, the total salary of marketing employees. So that is what we need to calculate here. So for this, we will use the formula SUMIF function, sum total only if it meets certain criteria. So you may now see it is a familiar thing in terms of syntax it asks for the range, then it asks for the criteria, and then it asks for the sum range. The first two essentially are similar to what we have done in MS Excel COUNTIF Function, so range here, in fact, starts from the department till the annual salary. We have to take it consecutively, so I am selecting the full range.
Recommended Articles
This article has been a guide to how to apply MS Excel COUNTIF function and SUMIF functions in practical ways using Microsoft Excel. These are the external link related to MS Excel, so go through the link for more details:
