Subtotal function for text
 

I'm working with data that is filtered and has a subtotal function at the top.

I have a column that shows manufacturer and would like to show the text
label of the first cell of a filtered list that appears. This is similiar to
the subtotal function that ignores hidden cells for numbers. Is there any way
to do this with text instead of numbers??

Thanks for your help.

Subtotal function for text
 

This is similiar to the subtotal function that ignores
hidden cells for numbers.

I'm not sure what that means but this array formula** will return the value
from the first cell of a filtered (or unfiltered) list.

Assume the full range is D2:D15.

=INDEX(D2:D15,MATCH(1,SUBTOTAL(3,OFFSET(D2:D15,,,R OW(D2:D15)-MIN(ROW(D2:D15))+1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Cecilia" wrote in message
...
I'm working with data that is filtered and has a subtotal function at the
top.

I have a column that shows manufacturer and would like to show the text
label of the first cell of a filtered list that appears. This is similiar
to
the subtotal function that ignores hidden cells for numbers. Is there any
way
to do this with text instead of numbers??

Thanks for your help.

I'm not sure what that means but this array formula** will returnthe value
 

That's fab for outputting the first row from a filtered list, but how do I output the second, third, fourth ect....

I can't figure out how this formula works or even if it's possible.

MTIA.

Jaes

On Tuesday, October 21, 2008 5:45 PM Cecili wrote:

I'm working with data that is filtered and has a subtotal function at the top.

I have a column that shows manufacturer and would like to show the text
label of the first cell of a filtered list that appears. This is similiar to
the subtotal function that ignores hidden cells for numbers. Is there any way
to do this with text instead of numbers??

Thanks for your help.


On Tuesday, October 21, 2008 11:35 PM T. Valko wrote:

I'm not sure what that means but this array formula** will return the value
from the first cell of a filtered (or unfiltered) list.

Assume the full range is D2:D15.

=INDEX(D2:D15,MATCH(1,SUBTOTAL(3,OFFSET(D2:D15,,,R OW(D2:D15)-MIN(ROW(D2:D15))+1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Cecilia" wrote in message
...

I'm not sure what that means but this array formula** will returnthe value
 

That's fab for outputting the first row from a filtered list, but how do I output the second, third, fourth ect....

I can't figure out how this formula works or even if it's possible.

MTIA.

James

On Tuesday, October 21, 2008 5:45 PM Cecili wrote:

I'm working with data that is filtered and has a subtotal function at the top.

I have a column that shows manufacturer and would like to show the text
label of the first cell of a filtered list that appears. This is similiar to
the subtotal function that ignores hidden cells for numbers. Is there any way
to do this with text instead of numbers??

Thanks for your help.


On Tuesday, October 21, 2008 11:35 PM T. Valko wrote:

I'm not sure what that means but this array formula** will return the value
from the first cell of a filtered (or unfiltered) list.

Assume the full range is D2:D15.

=INDEX(D2:D15,MATCH(1,SUBTOTAL(3,OFFSET(D2:D15,,,R OW(D2:D15)-MIN(ROW(D2:D15))+1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Cecilia" wrote in message
...


On Tuesday, April 12, 2011 5:02 AM James Hannaford wrote:

That's fab for outputting the first row from a filtered list, but how do I output the second, third, fourth ect....



I can't figure out how this formula works or even if it's possible.



MTIA.



Jaes