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...


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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.