Can you use an INDEX array in a COUNTIF formula ?

  • Thread starter Thread starter exceluser
  • Start date Start date
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.
 
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.
Click to expand...

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.
 
Back
Top