How to use the COUNTIFS Function

Friends, today I am going to show you how to use Microsoft Excel COUNTIFS step-by-step which works fast.

Download Link : MS Excel Download File
YouTube Link : How to use COUNTIFS Step-by-step

Description

The Microsoft Excel COUNTIFS function counts the number of cells in a range, that meets a single or multiple criteria.

The details are given in the following paragraphs but to make it easy let us first focus on the Syntax

Syntax

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2, ... criteria_range_n, criteria_n])

By supplying the parameters you can get the count the data based upon your Criteria, more than one. Its very simple but useful function, so, lets discuss how to use it. Criteria is for which data you want to count, Criteria Range is the range where this data exists.

In COUNTIF you get only one range and one Criteria but in COUNTIFS you will get numerous Criteria and Criteria range to select the data from multiple conditions.

Parameters or Arguments

criteria_range1

The range of cells that you want to apply criteria1 against.

criteria1

The criteria used to determine which cells to count. criteria1 is applied against criteria_range1.

criteria_range2, ... criteria_range_n

Optional. It is the range of cells that you want to apply criteria2, ... criteria_n against. There can be up to 127 ranges.

criteria2, ... criteria_n

Optional. It is used to determine which cells to count. criteria2 is applied against criteria_range2, criteria3 is applied against criteria_range3, and so on. There can be up to 127 criteria.

  As you can notice from the below image (second table) that I need the count of T-67 in two rows in two contions - 

1. Location Code T-67 & Employee Code E140
2. Location Code T-67 & Name is Shanta

Excel COUNTIFS function

And the formula here is : 

1. Location Code T-67 & Employee Code E140 -- "=COUNTIFS(A1:A14,A17,C1:C14,B17)"

1. Location Code T-67 & Name is Shanta -- "=COUNTIFS(C2:C14,B18,B2:B14,C18)"

One more thing please don't forget to protect your formula using Dollar $ sign.