• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Idle hands make mischief, just wondering about COUNTIFS

bobhc

bobhc

Excel Ninja
God day All


Idle mind playing with formula, I have been using a COUNTIFS formula and have been changing around the priority of the ranges and have not received different output. My question is, would there be a situation where the priority of range in the formula would make a difference.

The formulas I have been playing with are as :-


=COUNTIFS(U2:U25,X2,V2:V25,Y2)

=COUNTIFS(V2:V25,Y2, U2:U25,X2)


And the data I have used is something akin to this

[pre]
Code: 
U                 V                      X                Y          Z
Position	  Status	 	Position	   Status     Number
Chargerman	  Full Time	        Chargerman	  Full Time	3
Chargerman	  Trainee		Chargerman	  Trainee	4
Chargerman	  Full Time	        Furnace operator  Full Time	4
Chargerman	  Trainee		Furnace operator  Trainee	2
Chargerman	  Full Time	        Mill operator	  Full Time	4
Chargerman	  Trainee		Mill operator	  Trainee	2
Chargerman	  Trainee		Shear man	  Full Time	4
Furnace operator  Full Time	        Shear man	  Trainee	1
Furnace operator  Full Time
Furnace operator  Trainee
Furnace operator  Full Time
Furnace operator  Trainee
Furnace operator  Full Time
Mill operator	  Full Time
Mill operator	  Full Time
Mill operator	  Trainee
Mill operator	  Full Time
Mill operator	  Full Time
Mill operator	  Trainee
Shear man	  Full Time
Shear man	  Full Time
Shear man	  Trainee
Shear man	  Full Time
Shear man	  Full Time
[/pre]
 
No, priority does not matter in this formula. The SUMIFS and COUNTIFS function are using an "and" type logic, so all factors must be considered. Also, unlike some other programming languages, XL will not stop evaluating an "and" logic check if it finds a false, so there's no performance issues. Now, there's clearly a difference between these 2:

Code: 
=IF(AND(1=1,2=2,3=1),"good","bad")

and

=IF(1=1,IF(2=2,IF(3=1,"good","bad"),"bad"),"bad")

not counting how much wordier the 2nd is, it had to do 2 previous logic checks before determining the false. If the 3=1 had been first, then formula evaluates out. So, while some formulas can certainly care about priority, COUNTIFS does not.
 
Good day Luke M


That's what I thought but was just wondering, thanks for the confirmation
 
You must log in or register to reply here.
Back
Top