Look up formula in Excel 2010 table

I am looking to generate a table (see ‘table’ sheet) which will lookup a wind direction (eg N, NE, WSW etc) together with a wind speed range (eg between 0 – 5 knts) on a separate sheet of data and count the number of times these paramters are met.  In other words on ‘Table’ sheet cell C5 formula to lookup the specified wind direction (N) in the table row(s) and the specified table column wind speed(s) (0 – 5, 5-10 etc) knots and return the number of occurrences for the given direction and speed ranges. attempts at using vlookup have failed.

 In cell L5 on the same ‘Table’ sheet I am also looking to calculate the average speed for the various specified wind direction and combined speed ranges.

How might it be possible to dynamically change this table by selecting a specific time period such as year or month(s)?? to alter the various occurence values ?
Lookup-Table.xlsx
PVR101Asked:
Who is Participating?
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.

SteveCommented:
The attached use of COUNTIFS may do the first job.

We may be looking at a second and third question for the other two, just to keep the diferent issues seperate.

NOTE: you are missing the column for 10-20
U--Lookup-Table.xlsx
0
PVR101Author Commented:
Steve, thanks  - COUNTIFS works a treat and cheers for noting obvious omission.

Let me know if need ask separate questions regarding other queries but feel relevant to this one workbook and wanted to be clear on what was required from outset in case maybe impact on how resolved.  The plots are already dynamic - with the workbook using name ranges.
0
SteveCommented:
I have added the formula for average using AVERAGEIFS.

I have added a second table to demonstrate the addition of date selection.
( I thought a second table may be clearer to see the changes made )
U--Lookup-Table.xlsx
0

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:
Excellent - thanks Steve
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.