The COUNTIFS Formula
The COUNTIFS is the long awaited formula that counts cells that meets multiple conditions or criteria. The COUNTIFS function will work nicely with logical operators such as greater than > less than < does not equal <> is equal, wildcards * ? for partial cell matching. It is an improvement on the COUNTIFS formula as it adds an 127 conditions. This article explains the logic of the COUNTIFS formula with some practical examples.
COUNTIFS With Text Criteria
The COUNTIFS formula can be used to establish how many cells in a single range or multiple ranges meet a given condition or conditions. Let’s say we have a list of States in column A and we want to check how many States are equal to California and how many towns/cities are equal to Springfield. So the problem is we have a range of cities where Springfiled is the city/town so we need two criteria.
The above is a sample of cities that has California several times in column A. Our task is to count the amount of times that California appears in column A then count how often Springfield appears in column B. Any of the other Springfield criteria will be discounted if they do not have California as the State.
The Syntax for the COUNTIFS formula is as follows:
=COUNTIF ( Criteria Range, Criteria 1,[Criteria Range 2, Criteria 2])
The formula then repeats this syntax for part 3 and so on to part 127 but that is major over kill if you get that far. The following is how we might calculate the problem.
COUNTIFS with 2 Criteria
In the above example the formula result is:
=COUNTIFS(A2:A22,D2,B2:B22,E2)This returns the correct answer of 3 and because we have set up the workbook so we can choose other State and City/Town combinations to return the their count. It is a really powerful formula inside of Excel.
COUNTIFS with 3 Criteria
Let’s take the problem one step further and incorporate an operator into the equation. This example traps 2 text criteria and let’s say all the Breaches that are greater than or equal to >= 4.
The data set has grown but we can still trap our starting 2 conditions in the same way and we can incorporate our operator in the cells provided.
The formula can remain the same for the first two criteria, an additional column is brought in and we are only interested now in the first two California Springfiled entries as they have 4 and above breaches. The formula becomes.
=COUNTIFS(B2:B22,F2,C2:C22,G2,D2:D22,H2)
Notice how the >= is entered directly in cell. If you wanted to enter the operator directly in the formula it would need to look like the following:
=COUNTIFS(B2:B22,F2,C2:C22,G2,D2:D22,">="&H2)Where the operator is added with the ampersand symbol &.
">="&H2It is not all that intuitive but the more you do it the more you will get used to adding quotation marks and the & symbol. The following Excel workbook will help if you want to work through the above COUNTIFS problems based on multiple criteria.