Sumproduct & Filters

  • Thread starter Thread starter icsonu
  • Start date Start date
I

icsonu

I want to find out a Specific Word in a Column after I have applied a filter
to specific Column.

Column A Column B
Apple 95$
Apricot 120$
Banana 90$
Grapes 80$
*
*
*
And So On.

I want to display "Apple" in Cell C45 after I apply the filter to Column B
for say Fruits less than 100$, as the Column A display Grapes,Apple & Banana
too.
Can you pls help me out just to show the Apple in a Cell say C45, even
though banana & grapes are displaying in the Column A.

Regards
Sonu
 
Try this array formula** :

The entire unfiltered range is A2:A15.

=INDEX(A2:A15,MATCH(1,SUBTOTAL(3,OFFSET(A2:A15,,,ROW(A2:A15)-MIN(ROW(A2:A15))+1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
Thanks T. Valko the formula helped me a lot, but still I have a problem your
reference array MATCH(1, in the formula showed me the first word in the
column of the displayed filter result , Is there any way of finding the Exact
Text say "Apple" or "Grapes" which is also less than 100$.
 
Back
Top