2

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),"")

migrated from stackoverflow.com Mar 17 '14 at 1:16

This question came from our site for professional and enthusiast programmers.

3

=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 ""

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

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

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

2

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.

2

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.

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.