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

Ad

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.

## Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.