Free Microsoft Excel Tutorial - Excel COUNTIFS in Excel 2003

Microsoft Office Training > Excel Training > Free Computer Training >Excel Count Records Based on Multiple Criteria Using Array Formulas

Please feel free to use this content on your web site, but please include the links at the end

Excel Count Records Based on Multiple Criteria Using Array Formulas

Excel COUNTIFS in Excel 2003

If you are frustrated with the Excel COUNTIF function because it only allows you to specify a single criteria, you have two options:  one would be to purchase Excel 2007 and use the new COUNTIFS function and the other would be to use an array function in Excel 2003 or earlier.

To follow this example type the following data starting in A1 and using a separate column for each field.

We are going to calculate the number of current accounts held at the Brighton branch. 

To give you an idea of how array formulas work we will start off by performing a single criteria count by using a combination of an If function and Sum function. A COUNTIF function would perform the same calculation.

In cell E1 type the following formula =SUM(IF(A2:A12="brighton",1,0)), but use CTRL, SHIFT, ENTER to confirm the formula.  This will put brace brackets around your formula like this: {=SUM(IF(A2:A12="brighton",1,0))}.  The brace brackets indicate an array formula. This formula calculates that there are 6 accounts held at the Brighton branch, it has added up the value if true value “1” for every occurrence of Brighton in column A.

So how do we consider multiple criteria?  Well we need to nest another IF statement inside our current one so that we only up the value if true value “1” if both criteria are true. 

Here is the formula: {=SUM(IF(A2:A12="brighton",IF(B2:B12="current",1,0),0))}

Microsoft Office Training with Blue Pecan Computer Training. Tailored Microsoft Excel Training. Microsoft Excel Training Surrey