Applying formulas to autofiltered data

[ptwuk]

These are 2 formula that I am using


=COUNTIF(D3:D117,"<80%")


=SUMPRODUCT((D3:D117>=80%)*(D3:D117<82.5%)


I have now applied an autofilter to the data, however my calculations still calculate all the rows, including the hidden ones.


I have seen several suggestions to use SUBTOTAL with a function number, but none of these are suitable as they do not cover COUNTIF or SUMPRODUCT. As you can see the calculations are based on specific criteria.


Does anyone have any suggestions as to how I can resolve this problem?

 

[Hui]

Ptwuk

Can you include the Autofilter parameters in your Sumif or Sumproduct formula?

like: =SUMPRODUCT((D3:D117>=80%)*(D3:D117<82.5%)*(Autofilter criteria))


example say your autofilter was only showing values on a wednesday

=SUMPRODUCT((D3:D117>=80%)*(D3:D117<82.5%)*(Weekday(Date_Range)=4))

 

[ptwuk]

Not sure on the criteria to use. I have applied an autofilter to the headings in row 2. The headings are Dealer,Person, then Jan Feb Mar etc.


I want to filter by just one heading at a time, for example I could select just the Dealer to show his results and then the COUNTIF and SUMPRODUCT to output how many times he was under 80% and how many times he was betwwen 80 and 82.5%. I also want to select by person to show just his results.


Does this clarify what I need?

 

[ptwuk]

Just another observation - if I put the Autofilter criteria into the calculation, say Dealer, if I change the filter I would have to change the criteria in the calculation and again each time I change the filter

 

[ptwuk]

Just tried your suggestion on using the autofilter criteria - the Dealer names are in column A, in this case the filtered data was for a dealer named Harpers and it works!


=SUMPRODUCT((D3:D117>=80%)*(D3:D117<82.5%)*(A3:A117="Harpers"))


But as I have observed already, I would have to change all my formulas each time I appled an autofilter, which is not what I am looking for. I need to the output to change automatically when I apply the filter.

 

[kchiba]

Hi ptwuk,


With the formula you are using you do not have to filter as the formula works on all the data.


Have you tried using the Pivot Table


cheers


kanti

 

[cyrilz]

There is a post by Chandoo on filtering the data here :

http://chandoo.org/wp/2010/05/11/exclude-hidden-rows-from-totals/


Use a column with subtotal formula, and add it to your formula as the "Autofilter criteria".


Tada. ...

 

[ptwuk]

kchiba - thanks for your comments, but as already stated I would have to change the criteria in the calculation each time.


cyrilz - you pointed me in the right direction - grateful thanks. The link you gave shows how to use an extra column next to your data and write the formula =SUBTOTAL(102,B2). This formula returns “1″ if cell B2 is visible and “0″ if hidden.


I inserted an extra column as Column A (which I then hid, as it is not needed for any other purpose). All I then had to do was use the extra column in my calculation as follows:


=SUMPRODUCT((E3:E117>=80%)*(E3:E117<82.5%)*(A3:A117=1))


Note that I had to change the column refs from D3... to E3.... as I inserted the extra column. and it works perfectly.


Just as another tip, this solution will only display if there is something in column B. If like me you want to use multiple columns in your autofilter - say filter column B and then Column D the current solution will only check column B


To be able to use more - or even all the columns in your spreadsheet, simply add a further column and enter the number 1 in all the cells in that column (you can hide this column as well if you want as its only useful for this purpose).


Say this extra Column containing all the "1's" was Column G you would then use =SUBTOTAL(102,G2)in Column A as your calculation. When you then apply all your filters you will be left with the data you wante, all showing "1" in column A. Your calculation will then only include the data shown.


I hope this has been helpful, and I am grateful to all who have contributed.

 

[cyrilz]

@ptwuk : You DO NOT need to add several subtotal column, since the SUBTOTAL check for the visibility of the line.


This function (subtotal on column B) will return 0 in all the following cases :

- autofilter in column B that hide the line

- autofilter in any other column that hide the line

- manually changing the size of the line to 0


So only one column will do the job !

 

[ptwuk]

I did try just the one column first and found that if any cell in column B was blank it would still include it in the total, even though the row was hidden. The only way I found to work round this is the solution I gave. It may be that I have made an error somewhere