# Can you use an INDEX array in a COUNTIF formula ?

E

#### exceluser

Is it possible to use COUNTIF to count the number of values in an
INDEX array that are greater than 0 ?

A B
1 Fruit
2 Orange
3 Orange =COUNTIF(INDEX((INDEX(OFFSET(\$B3,-(ROW()-1),,ROW()-1,),)=
\$B3)*ROW(OFFSET(\$B3,-(ROW()-1),,ROW()-1,)),),">0")

The formula ends up evaluating to:

=COUNTIF({0,2},">0")

.... where the result is #VALUE!.

The value that I'm looking for is "1" since there's only one number in
the array that meets the COUNTIF criteria.

E

#### exceluser

Is it possible to use COUNTIF to count the number of values in an
INDEX array that are greater than 0 ?

A               B
1       Fruit
2       Orange
3       Orange          =COUNTIF(INDEX((INDEX(OFFSET(\$B3,-(ROW()-1),,ROW()-1,),)=
\$B3)*ROW(OFFSET(\$B3,-(ROW()-1),,ROW()-1,)),),">0")

The formula ends up evaluating to:

=COUNTIF({0,2},">0")

... where the result is #VALUE!.

The value that I'm looking for is "1" since there's only one number in
the array that meets the COUNTIF criteria.

CORRECTION

Is it possible to use COUNTIF to count the number of values in an
INDEX array that are greater than 0 ?

A B
1 Fruit Qty.
2 Orange
3 Orange =COUNTIF(INDEX((INDEX(OFFSET(\$A3,-(ROW()-1),,ROW()-1,),)=
\$A3)*ROW(OFFSET(\$A3,-(ROW()-1),,ROW()-1,)),),">0")

The formula ends up evaluating to:

=COUNTIF({0,2},">0")

.... where the result is #VALUE!.

The value that I'm looking for is "1" since there's only one number in
the array that meets the COUNTIF criteria.