Calculate Filtered Cells Only

[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.

 

[Brotha Lee]

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

 

[Picman]

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.

 

[T. Valko]

Where is this data?

 

[Picman]

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.

 

[T. Valko]

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))

 

[Picman]

That worked perfectly, thank you very much

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))

 

[T. Valko]

You're welcome. Thanks for the feedback!