# Count Values Based on Other Criteria

### Why do it?

Learning ways to count in Excel can save you time. Counting values in Excel using criteria is common in day to day business. For example; counting the number of failed products, overdue orders, staff in a certain department etc.

There are three methods that can be used to do this kind of count; COUNTIF, DSUM and SUMPRODUCT.

For example, Count all orders from Carlos Daly whose priority was High?

### Using COUNTIFS

• Syntax =COUNTIFS(criteria_rng 1, criteria1, [criteria_rng2, criteria2]…)

• Returns the count if criteria are met

• Criteria range 1 and Criteria 1 are mandatory. Additional criteria ranges and criteria are optional to a maximum of 127 range & criteria pairs

• All ranges should be of same length otherwise formula returns #VALUE error

### How It Works;

=COUNTIFS(C2:C23,F3,B2:B23,G3)=3

The formula evaluates each row at a time. If all the cells meet their associated criteria, the count increases by 1 and moves to the next row until all of the rows have been evaluated. If one cell does not meet the criteria, the formula will not increment the count

### Using DSUM

• Syntax = DCOUNT(Database, Field, Criteria)

• Database—this is all data inclusive of header rows. Data area should be structured in a way that Each Row is a Record, Each Column is a Field & Top rows contain headers that identify the fields

• Field –this is a column that holds ONE particular item of data e.g. Dates or Names. Can be omitted and still count records that meet all criteria.

• Criteria—this is section in the Worksheet (apart from the database) which holds Criteria. Criteria Area should be set in such a way that for every header, there is a cell below it where you enter the criterion to be met. The area MUST NOT include any Blank rows or Columns

### How It Works;

=DCOUNT(A1:D23,,F2:G3)=3

The formula counts the occurrences in the database “A1:D23” where “Customer Name = Carols Daly” and “Priority = High”

### Using SUMPRODUCT

• Syntax = SUMPRODUCT(Array1,Array2,…………,Array30)

• Multiplies parallel values in matching arrays and returns sum of their products

• Arrays must be of equal size and cannot be a mix of columns and rows

• Treats non-numeric values as zero

### How it Works:

=SUMPRODUCT((C2:C23=F3)*(B2:B23=G3))=3

(C2:C23=F3) Returns array of TRUE/FALSE depending on the customer name. SUMPRODUCT converts this Boolean array into its numeric equivalent of 1/0

(B2:B23=G3) Returns array of TRUE/FALSE depending on the order priority. SUMPRODUCT converts this Boolean array into its numeric equivalent of 1/0

SUMPRODUCT then multiplies the values of the two arrays and sums up the product

=SUMPRODUCT({1;0;0;1;0;0;0;0;0;0;0;1;1;0;0;0;1;0;0;0;1;0}*{1;1;1;0;0;1;1;1;0;0;0;1;0;0;0;0;0;0;0;0;1;1})