# Formulas to take filters into account

#### JamesE1993

##### New Member
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.

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### sparky2205

##### Active Member
Check out the SUBTOTAL() function. That may help.

#### JamesE1993

##### New Member
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

##### MrExcel MVP
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.

Replies
1
Views
157
Replies
6
Views
184
Replies
0
Views
155
Replies
3
Views
164
Replies
9
Views
167

1,127,197
Messages
5,623,310
Members
415,966
Latest member
ctorohuamanchumo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back