Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Using filters, counting the total of cells containing dates fitting a criteria of filtered data

Copper Contributor

Hello all, 

 

I have an Excel file with data containing personnel details with the focus of "courses completed" and "dates of expiry". The data set has two different departments contained within the table, and we filter to either department A or department B.

 

What I need to do, is to get a total of all those who are not yet expired, ("=>"&today()) and those expiring in 6 months (above but < +180), for only department A or B. I can do this using Count Ifs formulas with no issue for all data together; however this uses cell references (e3:e50 for example) so when filtering the table to only show Department A, the count still counts all cells and the figure doesn't change. When I try to use the table title as you would using SUM function - it shows up as an error.

 

I cannot seem to work out what formula or function to use, to account for the above "count ifs" functions required, using only the data that is filtered; as it isn't a simple "sum" function. 

 

I hope I have made this clear enough. 

 

Thank you in advance for any help. 

1 Reply

@Checkers 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?