If I include a condition like =if(countif(X:X,A1)>0,"found","not found") in an Excel formula, will Excel stop the countif after it finds one matching record, or will it keep looking to find the total count in the whole column, return that count, and check if that value is >0?

  • What are you trying to achieve here? Are you trying to search a value of A1 within a range X:X ? Then COUNTIF(X:X, A1) > 0 is enough – Vylix Apr 8 '17 at 3:05
  • Yes. The short of it. =COUNTIF(X1:X10, ">0") Will count all cells in X1:X10 that are greater than Zero. Hence, this will yield TRUE. Then you have this =IF(TRUE, TRUE, FALSE) – ejbytes Apr 8 '17 at 5:03
  • 1
    Lookup functions like VLOOKUP or MATCH stop as soon as they find a match. Count functions, like COUNTIF, go through all of the data in the specified range and count every qualifying one. They pretty much have to or every count function would never count higher than 1, which wouldn't be very useful. – fixer1234 Apr 8 '17 at 11:32
  • @fixer1234 that makes sense, I just wasn't sure if there might be some optimisation whereby knowing that they are testing a "greater than" condition, it aborts the count early. It's conceivable that they might code the calculation to inspect the whole formula for instances where continuing to execute the function inside is superfluous. – Some_Guy Apr 8 '17 at 15:47
  • 1
    Excel does that with logic like IF tests. It doesn't bother exhaustively analyzing branches that it has determined can't affect the result. – fixer1234 Apr 8 '17 at 17:27

It works from the inside out. COUNTIF returns a value, and then you are testing that value in the IF. The IF test is not part of the design of COUNTIF, and COUNTIF is not aware of what you are using the value for.

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