Home |
Search |
Today's Posts |
#1
|
|||
|
|||
{SUBTOTAL(2, IF(This = That, LIST))}
Given the following Column headers
Date Month ListPrice SalePrice Ratio I can use the following [ COUNT(IF(...)); AVERAGE(IF(...)); MEDIAN(IF(...)), etc A array functions to gain the following TERM COUNT MEAN MEDIAN 1 {COUNT(IF(TERM=Month,Ratio))} etc etc 2 ... 3 ... 4 ... 5 6 7 8 9 10 11 12 What I would like to know if there is a way to use SUBTOTAL(Integer,IF(...)). I have other column headers in the original list that are usefull to use DATA | FILTER | AUTOFILTER and would like the lower array to update automatically after using autofilter drill downs. TIA |
#2
|
|||
|
|||
Try the following formulas that need to be confirmed with
CONTROL+SHIFT+ENTER... =AVERAGE(IF((SUBTOTAL(3,OFFSET(MonthRange,ROW(Mont hRange)-MIN(ROW(MonthRa nge)),0,1)))*(MonthRange=MonthCriterion),AverageRa nge)) AND =MEDIAN(IF((SUBTOTAL(3,OFFSET(MonthRange,ROW(Month Range)-MIN(ROW(MonthRan ge)),0,1)))*(MonthRange=MonthCriterion),MedianRang e)) What would you like to count? In article , "doco" wrote: Given the following Column headers Date Month ListPrice SalePrice Ratio I can use the following [ COUNT(IF(...)); AVERAGE(IF(...)); MEDIAN(IF(...)), etc A array functions to gain the following TERM COUNT MEAN MEDIAN 1 {COUNT(IF(TERM=Month,Ratio))} etc etc 2 ... 3 ... 4 ... 5 6 7 8 9 10 11 12 What I would like to know if there is a way to use SUBTOTAL(Integer,IF(...)). I have other column headers in the original list that are usefull to use DATA | FILTER | AUTOFILTER and would like the lower array to update automatically after using autofilter drill downs. TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
loop trough e-mail address list to send task lists with outlook | Excel Discussion (Misc queries) | |||
LOOP - Adddress List -to email | Excel Discussion (Misc queries) | |||
Refresh a Validation List? | Excel Discussion (Misc queries) | |||
Extracting Values on one list and not another | Excel Discussion (Misc queries) | |||
Creating a list from an existing list. | Excel Worksheet Functions |