---

MS Excel 2003: Use an array formula to count the number of rows when column F and column L match criteria

This Excel tutorial explains how to use an array formula to count the number of rows when two columns match a critiera in Excel 2003 and older versions (with screenshots and step-by-step instructions).

Question: In Microsoft Excel 2003, I have a spreadsheet with data in rows 7 through 268 where Column G has different values (up to 10 different usernames), and column L has different values (up to 11 different codes for different types of orders). What I'm trying to do is have a formula say if G = SMITH, then count how many times the value EM appears in column L and display that number.

So by counting I know that EM appears 2 times in association with username SMITH, but I was hoping I could get a formula to do it so that I can apply it for all usernames.

Answer: Since you want to count the number of occurrences based on 2 conditions (a value in column G and a value in column L), you can do this with an array formula.

Let's look at an example.

First, we have our usernames in column G starting at row 7 and our order types in column L also starting at row 7. We want to count the number of rows where the value in column G is equal to "SMITH" and the corresponding value in column L is equal to "EM". And we want to do this for rows 7 through 268.

In cell N6, we've created the following array formula that uses the SUM function:

=SUM((G7:G268="SMITH")*(L7:L268="EM"))

When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows:

{=SUM((G7:G268="SMITH")*(L7:L268="EM"))}

This formula returns the number of occurrences where the username is "SMITH" and the order type is "EM".