Countifs formula using A:A or A2:A1000

  • Hi, is it bad practice when using countifs (or any excel formula) to select the whole column ie A:A, or is it better to just select the actual range ie A2:A100000.


    I understand using a table will make it dynamic etc....just want to know the pros and cons for each method.


    Thanks.

  • Most functions will only actually use the used range if you pass them an entire column, so it's not a problem. The issue is when you use an operator on an entire column - e.g. A:A="some value" - because that will look at every single row.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Yes, that's generally fine because COUNTIFS will only look at the part of A:A that intersects the used rows of the sheet. The full story is a little more complicated - have a look at https://fastexcel.wordpress.co…ge-good-idea-or-bad-idea/ for more information

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why