Sumproduct & Filters
 

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

Sumproduct & Filters
 

Try this array formula** :

The entire unfiltered range is A2:A15.

=INDEX(A2:A15,MATCH(1,SUBTOTAL(3,OFFSET(A2:A15,,,R OW(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.

--
Biff
Microsoft Excel MVP


"icsonu" wrote in message
...
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

Sumproduct & Filters
 

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

"T. Valko" wrote:

Try this array formula** :

The entire unfiltered range is A2:A15.

=INDEX(A2:A15,MATCH(1,SUBTOTAL(3,OFFSET(A2:A15,,,R OW(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.

--
Biff
Microsoft Excel MVP


"icsonu" wrote in message
...
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

Sumproduct & Filters
 

the formula showed me the first word in the
column of the displayed filter result

I thought that's what you wanted.

I don't understand what you want???

--
Biff
Microsoft Excel MVP


"icsonu" wrote in message
...
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$.

"T. Valko" wrote:

Try this array formula** :

The entire unfiltered range is A2:A15.

=INDEX(A2:A15,MATCH(1,SUBTOTAL(3,OFFSET(A2:A15,,,R OW(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.

--
Biff
Microsoft Excel MVP


"icsonu" wrote in message
...
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