Formulas to take filters into account

[JamesE1993]

Hi! So I've been trying to crack something that seems so stupidly simple that I'm almost embarrassed I'm having to post but I just simply cannot work this out. All I want to do is simply return values based off the filtered data. This is it...

Let's assume I filter the data in Column F to only show the data on EUR/AUD and GBP/JPY, as an example. I then need my metrics tabs (another sheet) to return the number of trades which occurred in may (first criteria) but only for the filtered pairs. This is the other tab...

I'd need the cell which is selected to then tell me how many trades occurred in May AFTER I've applied the filters of only showing the 3 pairs mentioned above. And so on for June, July....

Is this possible? At the moment the formula just counts the whole list, even after applying the filters.

 

[sparky2205]

Check out the SUBTOTAL() function. That may help.

 

[JamesE1993]

Check out the SUBTOTAL() function. That may help.

Thanks for the reply. I have looked at the function but I need to effectively add an IF criteria to it as well. That would count the number of trades after having applied the filter to the pairs but I'd need to it to count them only if the trade occurred in the respective month. Hope that makes sense

 

[FormR]

Hi, you could add an additional column to your first table called, for example, "Is filtered" that contains the formula:
=SUBTOTAL(102,[@[Open Date]])=0

At the moment the formula just counts the whole list, even after applying the filters.

Then you could amend this (countifs?) formula to include the additional criteria where the "Is Filtered" column is equal to FALSE.