Skip to main content

‘Countifs’ formula in excel and how to use it

Now you might be already aware of the countif function that most people use for their reports and excel sums. Again as I have explained in my previous blog about sumifs function,  the ‘countif’ function can fetch data using one exception whereas ‘countifs’ can match up to 29 criteria. 

Here, i will demonstrate you this function with the simple example:

Example1.

Below is the table we will be working on in this tutorial:

As you see in the above table, we have Year, Product, country, and total Sales. From this data table, we want to get our total sales using different criteria available in this table. 

In the above table, we need count of Red trolley Bags in the year 2019

Here is the formula syntax: COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2, criteria_range3, criteria3, … criteria_range_n, criteria_n] )”

In the above example, since we are looking for count of product Red trolley bags in 2019. Next, we have the first criteria range which year (2019) for us in this example, so we can select year range from A2:A37 (in Blue color) and you need to specify which criteria so we have selected F6 number raw which is 2019 for us in this example. Now next is the second criteria range which is a product name for us, so we have selected B2:B37 (In purple Color) and then criteria itself G6 which is product Red trolley Bags in this case.

Once we are done with the formula, we will get the value as desired (Refer to the below screenshot)

As we were looking for how many times Red Trolley bags comes in 2019 from our entire table and we got the expected value in one go with this ‘countifs’ function

Here is the image to verify if the resulted count is accurate or not.

And yes, wo got the correct count value with this function as highlighted in the above screenshot.

Now since we mentioned earlier you can use as much as 29 criteria to fetch data using this ‘countifs’ function. We will show you one more example.

Example 2.

Please see the below image where we used 3 criteria to get count from the same table used in earlier example. What we need is count of Green trolley Bags for 2019 in UK.

And we got the results here

That’s all. I hope you like this tutorial and find it easy to follow.

keep learning, keep improving. 

Labels:

Comments

Post a Comment

Popular posts from this blog

How to add CC recipients in Mail-Merge

Please refer How to do a Mail-Merge in MS Office to pursue this article further. I am going to explain the steps to send personalized emails to a huge group of receipts by looping people\groups in an email CC. Though, there is no straightforward technique for doing so. But there is one trick that functions like a charm. The trick is that we will create a new outlook rule which indicates whenever we send an email with the specific subject line, it will automatically include certain group\people in the CC. Steps to create this new rule in Outlook 1. Go to Rules in the menu bar and select Manage Rules & Alerts. 2. Then a dialogue box will appear. Click on the New Rule and choose Apply rule on messages I send in the following wizard (refer to the below screenshot) and Click Next. 3. In the subsequent window, tick the first line, which says with specific words in the subject line in Step 1 and click on a specific word from Step 2 (Refer to the below screenshot). 4. On clicking ...
Post a Comment
Read more

These instructions will allow you to save password-protected PDF attachments from Gmail to Google Drive.

Discover how to automatically save PDF attachments that require a password from Gmail to Google Drive and transform them into unencrypted versions that can be accessed conveniently washout a password. Financial statements are frequently sent by financial institutions as password-protected PDF files, including banks and credit card firms. Passwords formed from personal information, like your birthdate, Social Security number, or a special mix of details, are frequently used in these attachments. To access password-protected PDF files, you must enter the password each time. While Google Chrome allows for the permanent removal of passwords from PDFs, the manual and time-consuming nature of the process can be burdensome, especially if you have a substantial number of such attachments in your Gmail inbox. Imagine how easy it would be to have a solution that downloads all of your password-protected PDF attachments from Gmail straight to Google Drive and converts them into PDF files that aren...
Post a Comment
Read more

All you need to know about Service now ITSM Standard & ITSM Professional

ServiceNow offers two primary editions for its IT Service Management (ITSM) solution: ITSM Standard and ITSM Professional. Here's an explanation of each edition: ITSM Standard: ITSM Standard is the foundational edition of ServiceNow's ITSM offering. It provides essential ITSM capabilities to streamline service delivery and improve IT support processes. Key features of ITSM Standard include: Incident Management: Allows IT teams to log, track, and resolve incidents efficiently, minimizing service disruptions and reducing downtime. Problem Management : Helps identify the root causes of recurring incidents and facilitates proactive problem resolution to prevent future disruptions. Change Management: Enables IT teams to plan, assess, and implement changes in a controlled manner, reducing risks and maintaining service stability. Service Catalog : Provides a self-service portal where users can request IT services, track their requests, and access knowledge articles to find answers t...
Post a Comment
Read more