{SUBTOTAL(2, IF(This = That, LIST))}

D

doco

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
 
D

Domenic

Try the following formulas that need to be confirmed with
CONTROL+SHIFT+ENTER...

=AVERAGE(IF((SUBTOTAL(3,OFFSET(MonthRange,ROW(MonthRange)-MIN(ROW(MonthRa
nge)),0,1)))*(MonthRange=MonthCriterion),AverageRange))

AND

=MEDIAN(IF((SUBTOTAL(3,OFFSET(MonthRange,ROW(MonthRange)-MIN(ROW(MonthRan
ge)),0,1)))*(MonthRange=MonthCriterion),MedianRange))

What would you like to count?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

need help on either doing a loop and counting specific values orusing subtotal function 2
Counting duplicates\Frequency of duplicates 3
Subtotal command problem 1
COUNTIF & SUBTOTAL 2
function to count items in two columns 2
Looking for a function that will count the first instance of a 6
DAVERAGE, DMIN,etc from List 1
Using COUNTIF to count based on multiple cell criteria. 5

Top