Given NFL football data like the below, how many times does PHI go for it on 4th down? Here, "go for it" means attempt to gain enough yardage for another first down by passing or running the ball.
| A | B | C | |
| 1 | Team | Down | Play |
| 2 | ATL | 1 | RUSH |
| 3 | ATL | 2 | RUSH |
| 4 | ATL | 3 | PASS |
| 5 | ATL | 4 | PUNT |
| 6 | PHI | 1 | RUSH |
| 7 | PHI | 2 | RUSH |
| 8 | PHI | 3 | PASS |
| 9 | PHI | 4 | PASS |
| 10 | ATL | 1 | RUSH |
| 11 | ATL | 2 | RUSH |
| 12 | ATL | 3 | PASS |
| 13 | ATL | 4 | PUNT |
| 14 | PHI | 1 | RUSH |
| 15 | PHI | 2 | RUSH |
| 16 | PHI | 3 | PASS |
| 17 | PHI | 4 | RUSH |
The goal is to count the rows when:
- team = 'PHI'
- AND down = 4
- AND (play = "rush" OR play = "pass")
Here is the SUMPRODUCT forumla for this calculation, formatted for easier reading.
=SUMPRODUCT(
--(
(A2:A17="PHI")
* (B2:B17=4)
* ( (C2:C17="RUSH") + (C2:C17="PASS") )
)
)
You can see that multiplication is used for the AND, while addition is used for the OR.
The double negative has the effect of coercing the boolean true/false that is returned into an integer, where true = 1 and false = 0. The inner negative coerces to int, but leaves a negative value. The outer negative flips the sign back to positive. You can get the same effect just by multiplying by 1.
