MS Excel 2003: Use an array formula to count the number of rows that match 2 criteria

Question: In Microsoft Excel 2003/XP/2000/97, I have a workbook with 2 sheets:

Sheet1 has 0-to-many rows for one city (one row for each case opened in that city). Each row has a column that says that the case is closed (Y) or not closed (N).

Sheet2 is a summary for each city. I use the COUNTIF function to say how many times the city is found in Sheet1 (how many cases were opened in that city). I want another cell to say how many cases have Closed status for that city.

so... If city=Victoria AND closed=Y, then report the number of closed cases.

I've tried the COUNTIF function, SUMIF function, and IF function, but still can not get the correct answer. What should I do?

Answer: Since you want to count the number of occurrences based on 2 conditions, you can do this with an array formula.

Let's look at an example.

Download Excel spreadsheet (as demonstrated below)

First, we have two columns in Sheet1. The first column lists the City and the second column indicates whether the case is closed (Y/N).

On Sheet2, we've created a summary that lists the number of cases Closed/Open for each city.

In cell B2, we want to display the number of cases that are Closed for Victoria. To do this, we've created the following array formula:

=SUM((Sheet1!A2:A5="Victoria")*(Sheet1!B2:B5="Y"))

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

{=SUM((Sheet1!A2:A5="Victoria")*(Sheet1!B2:B5="Y"))}

This formula returns the number of occurrences where the city is Victoria and the case is set to Closed (Y).