0

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?

7
  • 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, 2017 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, 2017 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, 2017 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, 2017 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, 2017 at 17:27

1 Answer 1

4

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .