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

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

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.

`">"&0`

can also be written as`">0"`

which may be more transparent – barry houdini Mar 17 '14 at 0:35