Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to find the filter criteria
I have a worksheet which has in column A2:A9 a label and in column
B2:B9 a value, like so: A1="LABEL" B1="VALUE" A2="LabelGreen" B2=452.47 A3="LabelBlue" B3=-87 A4="LabelRed" B4=9 A5="LabelGreen" B5=-7 A6="LabelBlue" B6=1888.97 A7="LabelRed" B7=144 A8="LabelGreen" B8=-0.02 A9="LabelBlue" B9=87002 I use the autofilter on column A to select only certain labels. in cell D1 I use the subtotal formula to give me the sum of all filtered values. D1=SUBTOTAL(9,B2:B9) This works fine, for example, if I filter on column A for "LabelGreen", the formula in D1 calculates 452.47 - 7 - 0.02 = 445.45. What I am trying to do now and what I have not yet found a solution for is: In cell C1 I want to display the criteria I have filtered for. For example, when I filter column A for "LabelGreen", I want "LabelGreen" to be displayed in cell C1. I was thinking something similar to SUBTOTAL might do the trick, for example: C1=SUBFIRST(9,A2:A9) But this only gives me (and I am not surprised) an error "#NAME?" Does anybody have an idea ? |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Database functions should use criteria in formula, as 1-2-3 does | Excel Worksheet Functions | |||
Data Validation - Scroll in the formula bar for a custom criteria | Excel Worksheet Functions | |||
Array Formula w/ Multiple SumIf Criteria | Excel Worksheet Functions | |||
Formula to return ADDRESS of cell in range that meets criteria | Excel Worksheet Functions | |||
"find" and "filter" working weirdly | Excel Discussion (Misc queries) |