Calculate Filtered Cells Only
 

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.

Calculate Filtered Cells Only
 

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" wrote:

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.

Calculate Filtered Cells Only
 

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.

"Brotha Lee" wrote:

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" wrote:

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.

Calculate Filtered Cells Only
 

Where is this data?

--
Biff
Microsoft Excel MVP


"Picman" wrote in message
...
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.

"Brotha Lee" wrote:

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" wrote:

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.

Calculate Filtered Cells Only
 

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" wrote:

Where is this data?

--
Biff
Microsoft Excel MVP


"Picman" wrote in message
...
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.

"Brotha Lee" wrote:

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" wrote:

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.

Calculate Filtered Cells Only
 

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

--
Biff
Microsoft Excel MVP


"Picman" wrote in message
...
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" wrote:

Where is this data?

--
Biff
Microsoft Excel MVP


"Picman" wrote in message
...
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.

"Brotha Lee" wrote:

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" wrote:

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.

Calculate Filtered Cells Only
 

That worked perfectly, thank you very much

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Picman" wrote in message
...
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" wrote:

Where is this data?

--
Biff
Microsoft Excel MVP


"Picman" wrote in message
...
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.

"Brotha Lee" wrote:

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" wrote:

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.

Calculate Filtered Cells Only
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Picman" wrote in message
...
That worked perfectly, thank you very much

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Picman" wrote in message
...
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" wrote:

Where is this data?

--
Biff
Microsoft Excel MVP


"Picman" wrote in message
...
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.

"Brotha Lee" wrote:

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" wrote:

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.