setting range criteria in pivot table

  • Thread starter Thread starter ela
  • Start date Start date
E

ela

Can I freely set a range criterion(a) for counting? e.g. for the following
data, can I set < 80 or > 100 or <120 and > 80 DYNAMICALLY? I can do that by
adding columns to check the conditions but it then constrains my mind as I
cannot see the statistics in real time. Thanks.

GFR_Base
80.161
86.994
128.392
108.492
126.251
 
Hi,

Sure, try something like this

=SUMPRODUCT(--(A1:A5>=D1),--(A1:A5<=E1))

Where your data is in range A1:A5 and you enter a lower and upper bound in
D1 and E1.

Dynamic!

If this helps, please click the Yes button.

Cheers,
Shane Devenshire
 
ela said:
Can I freely set a range criterion(a) for counting? e.g. for the following
data, can I set < 80 or > 100 or <120 and > 80 DYNAMICALLY? I can do that
by adding columns to check the conditions but it then constrains my mind
as I cannot see the statistics in real time. Thanks.

GFR_Base
80.161
86.994
128.392
108.492
126.251
Click to expand...


What I did exactly is the post above mine "Calculated field -pivottable",
creating a new column similar to muddan madhu suggested. In fact Shane
Devenshire provided a complete solution, if Excel does not limit the no. of
col. to 255. So for continuous data like mine, anybody has a solution?
 
Back
Top