setting range criteria in pivot table
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 |
setting range criteria in pivot table
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" wrote: 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 |
setting range criteria in pivot table
"ela" wrote in message ... 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 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? |
All times are GMT +1. The time now is 09:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com