Formulas to take filters into account

JamesE1993

New Member
Joined
Oct 14, 2020
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
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...
Excel.jpg

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...
Screenshot 2020-10-14 at 08.39.17.png

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.
 

Some videos you may like

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
Joined
Feb 6, 2013
Messages
296
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Check out the SUBTOTAL() function. That may help.
 

JamesE1993

New Member
Joined
Oct 14, 2020
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Aug 18, 2011
Messages
6,480
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top