# How to count based upon multiple conditions

I am trying to create the following formula

``````=SUM(IF(AND(K3:K138="Tekken",L3:L138="American Football"),1))
``````

However, it always seems to result in zero, even where a single row would have both values for Tekken and American Football. I have gotten the simpler formula to work...

``````=SUM(IF(K3:K138="Tekken",1))
``````

This formula works fine and counts the rows that have values for Tekken. I am wondering why this works, but the former results in a value of 0. I have rows that have both values and should represent a 1.

Does there have to be special use of AND when dealing with Arrays?

• Why does the second function work in that case? – jab May 18 '13 at 6:14
• Amer has given you the best approach. AND doesn't work as required with array formulas because it returns a single result (TRUE or FALSE) rather than an array of results, so `AND(K3:K138="Tekken",L3:L138="American Football")` is TRUE only when all values in all rows are the designated text values. In array formulas you can simulate AND with * so this version will work - `=SUM(IF((K3:K138="Tekken")*(L3:L138="American Football"),1))` – barry houdini May 18 '13 at 10:08
• Note: for the 2nd version COUNTIF is better `=COUNTIF(K3:K138,"Tekken")` – barry houdini May 18 '13 at 10:10

You'll find the `COUNTIFS` function will be better suited for you.

So try this out:

`=COUNTIFS(K3:K138,"Tekken",L3:L138,"American Football")`

The `COUNTIFS` function allows you to select multiple ranges and the criteria you want to associate with that particular range. Then gives you a sum of all the ranges that match all the criteria.

That said, the reason why your function with `AND` does not work is that `AND` is expecting all the values in the range to equal "Tekken" (or "American Football" for the other range) before it will return a TRUE value.

Instead, if you want to use `SUM` and `IF` statements, you would have to nest your `IF` statements, into something like:

`=SUM(IF(K3:K138="Tekken",IF(L3:L138="American Football",1,0),0))`

Just to clarify for other readers, the functions with `SUM` will only work if you press Ctrl+Shift+Enter when entering the formula. This makes the formula into an array formula and it will gain `{` `}` around the formula. But the `COUNTIFS` formula does not need to be an array function.