---

MS Excel 2007: Use an array formula to count matches on multiple values in one column

This Excel tutorial explains how to use an array formula to count matches on multiple values in one column in Excel 2007 (with screenshots and step-by-step instructions).

Question: In Microsoft Excel 2007, I want to count the the word Good in column B if column A matches Team 1 or Team 2.

Column A	Column B
Team 1	Good
Team 2	ok
Team 3	Good

I used the following to match on just one value from Column A.

=SUM((A1:A3="Team 1")*(B1:B3="Good"))}

How can I match on multiple values from Column A?

Answer:You are very close with your array formula.

In your example, you have counted the number of times that column A contains Team 1 and column B contains Good. Now you need to add to your formula, the case where column A contains Team 2 and column B contains Good.

Here is an array formula that will work that uses the SUM function:

=SUM((A1:A3="Team 1")*(B1:B3="Good"))+SUM((A1:A3="Team 2")*(B1:B3="Good"))

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

{=SUM((A1:A3="Team 1")*(B1:B3="Good"))+SUM((A1:A3="Team 2")*(B1:B3="Good"))}

This formula will add together number of times that column B contains Good when column A contains either Team 1 or Team 2.

Please note that the formula above only evaluates rows 1 to 3, you will need to adjust the formula accordingly. If you wish to evaluate the entire column A and B, you could use the following formula instead:

{=SUM((A:A="Team 1")*(B:B="Good"))+SUM((A:A="Team 2")*(B:B="Good"))}