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 organise, format and calculate data smoothly. Develop skills to master excel tools, formulae and function. 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 department 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 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 these 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 2 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 countif 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 way 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 its 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 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:
