Gfrktyl

I have the below formula, but don't understand why the last part of it ,">"&0),""). Can somebody clarify pls?

=IF(COUNTIF($AJ$2:$AJ9,$AJ9)=1,COUNTIFS($AJ$2:$AJ11323,$AJ9,$AQ$2:$AQ11323,">"&0),"")

=IF(COUNTIF($AJ$2:$AJ9,$AJ9)=1,COUNTIFS($AJ$2:$AJ11323,$AJ9,$AQ$2:$AQ11323,">"&0),"")

• We can break this up - IF(Logical Test, True condition, False condition) - the false condition is your last ""
  
  
  
  
  • if function
  
  
  
  

• Your Logical test is countif() = 1 and your true condition is countifs > 0
  
  
  
  
  • countif function
  
  
  
  

• Your true condition is countifs(first range, first criteria, second range, second criteria)
  
  
  
  
  • 
    countifs function
  
  
  
  

• This means AJ2:AJ11323 = AJ9 is criteria 1 and AQ2:AQ11323 >0 is criteria 2
  

The end of your formula is the second criteria of countifs and the false condition of your if.

You have

=IF(Condition, ValueIfTrue, ValueIfFalse)

So the empty string "" at the end is just the value to take if COUNTIF(...) <> 1.

The other part you don't understand is an argument to COUNTIFS, which is much like COUNTIF but with multiple ranges and conditions. Basically, it counts all the rows 2 <= n <= 11323 where AJn = AJ9 and AQn = ">"&0.

From the MS Office help on the COUNTIFS function:

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

The COUNTIFS function syntax has the following arguments:

• 
  criteria_range1 Required. The first range in which to evaluate the associated criteria.


• 
  criteria1 Required. The criteria in the form of a number, expression, cell reference, or text that define which cells will be counted. For example, criteria can be expressed as 32, ">32", B4, "apples", or "32".


• 
  criteria_range2, criteria2, ... Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.

Let's consider your case (only the last COUNTIFS, of course):

COUNTIFS($AJ$2:$AJ11317,$AJ3,$AQ$2:$AQ11317,">"&0)

The first criteria_range, criteria1 pair is $AJ$2:$AJ11317, $AJ3 while the second criteria_range2, criteria2 pair is $AQ$2:$AQ11317, ">"&0. criteria2 (or ">"&0) evaluates to ">0" (">"&0 is equivalent to ">0") which is true if any of the values in $AQ$2:$AQ11317 is greater than 0.