Excel - only show/use data based on dates selected/entered

Kelly,
There is a way, it is utilizing AVERAGEIFS rather than just AVERAGEIF. In this function we can use > or < logic functions to help filter out data in the condition arguments.

Attached is the sheet with the new formulas. The other thing I added was a few IFERROR functions, so if there are not any values within a date range you will see a 0 rather than an error message.

Let me know if you have any questions.
date-TC-020117.xlsx

Haven't looked at the file but sounds like you might be able to use a Pivot Table.

Very handy for selecting specific criteria for data to summarise.

Hi Rob,
My apologies... i am just getting back to this no....
I just looked at the pivot table and WOWWWWW!!!!! I think this will totally work....just need to understand how you did it! (it looks super complicated!)

thanks
Kelly

Not complicated at all really.

Away from desk at the minute but happy to post some notes when back.

awesome thanks! :)

I have been reading about the pivot tables and trying to duplicate the one you sent....so far I have not been able too :(

I dont understand where/how you added the "Year"
I dont know how you get the std deviation to work
I dont know how you got yours too look that way....neat/tidy! my attempt is a mess to look at!:(

1) Convert Data to Table. Select a cell in the data and press Ctrl + T. The Table conversion wizard will pop-up and should have selected the extent of the data. Make sure the "My data includes headers" box is ticked and click OK. The data should then get the default Table formatting (alternate blue and white rows).

2) Create Pivot Table. With the cursor still in the data table, from the Insert ribbon choose Pivot Table and then from the drop-down choose Pivot Table. In the Pop-up that appears the data range should already say "Table1". If it gives the actual range of data just overwrite with "Table1" (without the quotes). Click OK and it will create the blank pivot on a new sheet.

There will now be a navigation pane on the right hand side of the screen showing the headers of the data table in a list at the top of the navigation pane and four smaller panes at the bottom. The four smaller panes represent the four areas in which you can put the headers from the data. For this we will only be using three of them: Column Labels, Row Labels and Values. Select the data headers and drag them one at a time to the relevant smaller panes:


Now right click on one of the Date entries and choose Group from the Right Click menu. Select Days, Months and Years and click OK. This will create groupings by Year and Month with detail by Day available if required. Clicking OK the Pivot will shrink to only a few columns. This should automatically add the Years and Months columns.

From this point you are now ready to update the Data.

As you add data to the table the formatting should automatically carry down to the new data, showing that it is being included in the Table. If it does not automatically expand, the bottom right corner of the bottom right cell of the Table will show a blue triangle, click and drag this down to include the new rows. The Pivot Table will not automatically refresh with new data. Select the Pivot Table and right click on a cell. From the Right Click menu select Refresh and the new data will be included.

I hope that covers everything but by all means post back if you need help with anything.

Thanks
Rob H

thanks sooooo much! :):):)

You made my day!  thanks for all the extra explaining on the pivot tables:)

No worries, glad to help.

Pivot Tables can be very powerful when you get to know how to use them.

In later versions of Excel there is also a thing called Power Pivot which allows for masses of data to be summarised, more than a standard excel sheet can handle. I will admit that I know very little about this feature.

Thanks
Rob