Filtering data table by months

I am looking to filter a data table and its output results which include COUNTIFS functions using excels date filters option.  

How can I modify the cells in C27:K42 (HIGHLIGHTED YELLOW) on 'Table' Sheet so that the data changes when i apply a date filter on column B in the 'Metocean Data' sheet for say months may/jun/jul/aug? or indeed a single month so that all dates for these specific month(s) are only included in the countifs fuction calc.

The workbook already allows for filtering based on a specified start and end date but looking to modify so includes just specified month(s) for all the years of data. Ideally I would like to be able to just apply the date filter on the data sheet and the table for occurences update automatically

Also, is there a way to select more than one month when use date filters>all dates in the period>may+june+jul+aug etc as seems to only allow single month selection?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kgerbChief EngineerCommented:
Hello PVR101,
Take a look at the attached workbook.  I created a pivot table that I think does that you want.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PVR101Author Commented:
Hi Kyle - many thanks - i had dabled with pivot tables but not figured out how to apply as wish.  I take it that what i am trying to do is only possible by using a pivot table and creating additional data coulmns as proposed. ? I have a couple of Q's so greatful if able to advise/assist further.

The pivot table appears to automatically list the directions in below format;


but other tables in my workbook use the following;

N       NNE       NE       ENE      E      ESE      SE      SSE      S      SSW       SW      WSW      W      WNW      NW      NNW

is it possible so when pivot table is created it presents results in similar format ?

In my full workbook I have generated some plots based on the countifs table results for Wind speed categories Vs % Occurence, Wind direction vs % occurences, Period Vs % exceedance, Wind speed knots Vs % exceedance etc.  Is it possible to generate any of these using a pivot table?

It seems i need create a new pivot table for each plot as if attempt to create a second plot and select different field list items both plots on sheet will change.  Apologies for ignorance re pivot tables - hence queries ?

kgerbChief EngineerCommented:
Hello PVR101,
Sorry for the late response, got busy with work stuff.  

You can sort a pivot table using a custom list.  This video shows you how to do it.

Alternatively, you can manually drag the items into a new position.  Just select one of the cells (N NNE NE ENE etc) and drag them to a new spot.

Regarding creating the plots, yes you'll have to create a new pivot table for each chart that shows different data.

PVR101Author Commented:
Hi Kyle, thanks for reply and vid which was very helpful.  I have sent you a message with a sample sheet with just a couple of small queries and needed to forward sheet to illustrate - hope to hear back from you shortly :)

much appreciated
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.