Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 ... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel subtotal function- put subtotals in bold text | Excel Discussion (Misc queries) | |||
Need subtotal function to return text as result | Excel Discussion (Misc queries) | |||
Embed a countif function in subtotal function? | Excel Worksheet Functions | |||
How do I nesting subtotal function within average function in Exc | Excel Worksheet Functions | |||
Subtotal function (again) | Excel Worksheet Functions |