Calculate Filtered Cells Only

  • Thread starter Thread starter Picman
  • Start date Start date
P

Picman

I have a list of value that i've built formulas to count the number of
occurrences of values that fall into curtain ranges. Now I what to filter the
data and do the calculations on the filtered values only, and not include the
other values.
 
Picman,

Use the subtotal function. it should be something like this
= subtotal(102,A:A) to count all values in column A.

If filtered it will ignore the hidden values
 
The problem is that i'm totaling the number of values that fall between
curtain ranges, how many are between 100 to 149, and ,150 to 199, 200 to
249.....etc.
 
Below is a sample of the data, each are seperate columns.

CUST # City Prov Format Sales # of Ord AVG

0001 Concord ON 2K3 $697.40 2 $348.70
0003 Toronto ON 2K3 $2,953.65 5 $590.73
0004 Oakville ON Dover $2,190.32 5 $438.06
0005 Kitchener ON Dover $2,333.30 6 $388.88
0006 Brampton ON 2K3 $561.29 3 $187.10
0007 Nepean ON 2K3 $2,144.14 7 $306.31
0008 London ON 2K3 $1,568.22 7 $224.03
0009 London ON 2K3 $1,383.12 7 $197.59
0010 Sudbury ON 2K3 $1,297.87 3 $432.62
0011 Etobicoke ON 2K3 $2,442.54 7 $348.93
0012 Etobicoke ON 2K3 $1,566.27 4 $391.57
0014 Burlington ON 2K3 $1,341.99 3 $447.33
0015 Toronto ON 2K3 $3,290.90 7 $470.13
0016 Ottawa ON 2K3 $2,852.58 8 $356.57

it is the last column that i want to determine the number of records that
fall into specific ranges as mentioned before.
 
Try this...

Assume the full unfiltered range is A3:G16

To count the filtered or unfiltered values in column G that are >=400 and
<=499:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(G3:G16,ROW(G3:G16)-ROW(G3),0,1)),--(G3:G16>=400),--(G3:G16<=499))
 
That worked perfectly, thank you very much

T. Valko said:
Try this...

Assume the full unfiltered range is A3:G16

To count the filtered or unfiltered values in column G that are >=400 and
<=499:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(G3:G16,ROW(G3:G16)-ROW(G3),0,1)),--(G3:G16>=400),--(G3:G16<=499))
Click to expand...
 
Back
Top