Doing calculations on data that is auto filtered

  • Thread starter Thread starter Glenn Mulno
  • Start date Start date
G

Glenn Mulno

Hi All,

I am trying to do some calculations on some data that is auto filtered. I
tried using the SUBTOTAL function but it is limiting what I need to do.

For example - my spread sheet is has around 9000 rows of data in it.

I have auto filtering on which reduces this to around 50 rows of relevant
data.

Within the data that is left I am trying to do the following:
Count the number to entries that are >= 8. I had this prior to auto
filtering:
=COUNTIF(Sheet1!T2:Sheet1!T25000,">=8")

Count the number of entries that are below 8. I had this prior to auto
filtering:
=COUNTIF(Sheet1!T2:Sheet1!T25000,"<8")

What the above are doing is ignoring the filtered data and counting within
all 9000 rows. I tried the SUBTOTAL function but it only has a limited
number of functions that it works with and COUNTIF is not one of them.


I tried this wacky code:
=SUBTOTAL(2,IF(Sheet1!T2:Sheet1!T25000 >=8,1,0))
But the cell only displays #VALUE!

Out of desperation I tried playing with the two functions :
=SUBTOTAL(2, COUNTIF(Sheet1!T2:Sheet1!T25000,"<8"))
=COUNTIF(SUBTOTAL(2, Sheet1!T2:Sheet1!T25000),"<8")

but excel just won't use it's powers of mind reading to properly interpret
what I am trying to do.

Any thoughts on getting around this? I could get the data by customer
filtering a couple times but I was hoping to get it to display all at the
same time. Also - I have a few other things I am trying to do with the
filtered data and that solution would not work well. If I can get around
this issue then I can work out all else.

Thanks,
Glenn
 
Try...

=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(T2:T2500,ROW(T2:T2500)-MIN(ROW(T2:T2500)
),0,1))>=8))

Hope this helps!
 
Replace my formula with the following...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(T2:T2500,ROW(T2:T2500)-MIN(ROW(T2:T2500)),0
,1)),--(T2:T2500>=8))

and

=SUMPRODUCT(SUBTOTAL(3,OFFSET(T2:T2500,ROW(T2:T2500)-MIN(ROW(T2:T2500)),0
,1)),--(T2:T2500<8))

Hope this helps!
 
Thanks Domenic!!! That is exactly what I needed.

I did have to make one slight adjustment to each to accommodate some of the
cells having "NA" in them which apparently get counted in the above 8
formula. They must get converted to their ASCII value before counting or
something.

Final formula's used were:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet1!T2:Sheet1!T25000,ROW(Sheet1!T2:Sheet1!T25000)-MIN(ROW(Sheet1!T2:Sheet1!T25000)),0,1)),--((Sheet1!T2:Sheet1!T25000>=8)*(Sheet1!T2:Sheet1!T25000<11)))

And

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet1!T2:Sheet1!T25000,ROW(Sheet1!T2:Sheet1!T25000)-MIN(ROW(Sheet1!T2:Sheet1!T25000)),0,1)),--((Sheet1!T2:Sheet1!T25000>=0)*(Sheet1!T2:Sheet1!T25000<8)))

I adjusted the <8 formula to not go below 0. I do not see anything going in
there that would be less than 0 but I figured it was just safer to do so.

Thanks again!
 
Glenn,

Just a few thoughts...

You don't need to repeat the sheet name for each range (at least not in
my version of Excel). Also, personally, I like to keep the syntax for
the formula consistent. Therefore...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet1!T2:T25000,ROW(Sheet1!T2:T25000)-MIN(
ROW(Sheet1!T2:T25000)),0,1)),--(Sheet1!T2:T25000>=8),--(Sheet1!T2:T25000<
11))

Now, as far as the above 8 formula, you can have the following...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet1!T2:T25000,ROW(Sheet1!T2:T25000)-MIN(
ROW(Sheet1!T2:T25000)),0,1)),--(ISNUMBER(Sheet1!T2:T25000)),--(Sheet1!T2:
T25000>8))

Hope this helps!
 
Back
Top