Excel Count If With Multiple Criteria
There are many times that it become necessary to COUNT cells based on multiple criteria. The examples below will show you 3 ways that this can be done. However, often the most efficient method is to use a Pivot Table If you are not familiar with Pivot Tables, I cannot stress enough how much easier spreadsheet life becomes once you are!
If you are not already aware, the Excel COUNTIF formula/function can only check to see if specified cells meet one condition, e.g.
COUNTIF Syntax
=countif(range,criteria)
=COUNTIF(A1:A20,">20")
Which would COUNT all numeric cells in the range A1:A20 where values were greater than 20.
Note the criteria argument is in the form of a number, expression, or text that defines which cells will be counted. For example, criteria can be expressed as 20, "20", "=20", ">20", "North", "N*".
=COUNTIF($A$1:$A$20,20), =COUNTIF($A$1:$A$20,">20"), =COUNTIF($A$1:$A$20,"North"), =COUNTIF($A$1:$A$20,"N*")
Ok, so if we need to count a range of cells where corresponding cells (on the same row but different column) meet 1, or more conditions we can no longer use the COUNTIF. The other formulas we can use, in order of their efficiency, are
1) DCOUNT & DCOUNTA Download advanced examples of DCOUNT
2) SUM as an array formula
3) COUNT with and IF function nested and entered as an array formula .
DCOUNT will count only numeric cells where the cells, or corresponding cells meet a specified criteria.
DCOUNTA will count all cells (Text or numeric) where the cells, or corresponding cells meet a specified criteria.
For all examples I will use the data as shown below. Where A2:E25 has been named: DataTable
DCOUNT
Count the numbers in a column of a list, or database, that match criteria you specify. For example;=DCOUNT(DataTable,B2,Criteria)Would Count all cells in B2:B25 that meet the criteria is the named range: Criteria (shown below)
The top row of the range: Criteria has exact copies of the headings in the range DataTable . The reference to cell B2 is telling the DCOUNT to count the numbers in B2:B25 that meet the criteria. We could replace the reference to B2 with the text "Quantity", or the number 2 as the "Quantity" column is the second column in the table.
The criteria text "Bourbon" and "Vodka", under the criteria table heading "Description", tells DCOUNT that either "Bourbon" OR "Vodka" is a match. The same principle is used for the "Alcohol Content", i.e. "High" OR "Low". This is then seen by DCOUNT as an OR condition.
Note the repeat of the date under "Use By Date". This is needed when using more than 2 rows as the criteria as a blank cell is seen as a wildcard character. If we wanted to count only data that lies between 2 dates, we would need have 2 "Use By Date" headings in our Criteria range and use: >7-Apr-2005 below one of these headings and <7-Jun-2005 under another. This is then seen by DCOUNT as an AND condition.
Download advanced examples of DCOUNT
DCOUNTA
If we changed the above DCOUNT example to:
=DCOUNT(DataTable,A2,Criteria)
We would always get a result of 0 (zero) regardless of the criteria being met, or not. This is because DCOUNT will only ever count all numeric cells and there are none in column A under the "Description" field.
To get a count of these cells, we would need to use the DCOUNTA function which would count all cells, text or numeric, where the criteria is being met. That is;
=DCOUNTA(DataTable,A2,Criteria)
SUM as an array formula
Normally, the SUM function will add all numeric cells in a specified range. However, when used as an array formula with criteria used, it will give us a count instead of a sum. See below example=SUM((A2:A25="Vodka")*(C2:C25>VALUE("7-Apr-2005"))*(E2:E25="High"))+SUM((A2:A25="Bourbon")*(C2:C25>VALUE("7-Apr-2005"))*(E2:E25="Low"))
As with the DCOUNTA example, the above array entered (Ctrl+Shift+Enter) SUM example would count all rows where the "Use By Date" is greater than 7-Apr-2005, the "Description" is either "Vodka" OR "Bourbon" and the "Alcohol Content" is "High" OR "Low".
The reason it gives a count is because each check is returned as TRUE (has a value of 1) or FALSE (has a value of 0). So, in the above example, the third row check would actually look like;=SUM((0)*(0)*(1))+SUM((1)*(1)*(1))As you can see, unless all 3 criteria are met in at least one of the Sum functions, the result will always be 0 (FALSE). To read about this in detail, see our April edition of our free Excel Newsletter
COUNT and IF
=COUNT(IF(A2:A25="Bourbon",IF(C2:C25>VALUE("7-Apr-2005"),IF(E2:E25="Low",B2:B25))))+COUNT(IF(A2:A25="Vodka",IF(C2:C25>VALUE("7-Apr-2005"),IF(E2:E25="High",B2:B25))))
The above, does the same as the array SUM example and must be entered by pushing Ctrl+Shift+Enter. Note we have told the COUNT to count all cells in B2:B25 where the criteria is met. In other words, count all "Quantity" cells where the rest of the specified rows of the "Description", "Use By Date" and "Alcohol Content" meet the specified criteria
It is important to know that using the array entered COUNT and IF, or the SUM as an array formula over a large number of cells will cause a noticeable slow down in Excel's recalculation time. The DCOUNT & DCOUNTA are far more efficient in this regard. However, as stated at the very Start , a Pivot Table is better still when done correctly.
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
