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 the formula that you would need to use:
=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"))}
