Jump to content


Photo

Lookup information within Date range and Sum

Lookup Date range Sum Filter

  • Please log in to reply
1 reply to this topic

#1 Taskmas7er

Taskmas7er

    Newbie

  • Members
  • Pip
  • 2 posts

Posted 06 May 2016 - 09:13 AM

I recently completed your EXCEL 2010 Expert Course! Fantastic learning resource!

 

I have an issue with tool i'm trying to create.

 

Context:

I manage school trips across the world.  i am building an analysis tool which easily shows all the various details associated with team i.e. total travelling, departure date etc.

 

I want to be able to select a Destination i.e. Morocco, and a Date Period i.e. 01/10/2015 - 01/01/2016. and the relevant information from within that period is calculated.

 

So effectively i want the formula as such to say Look up (Destination) within (Date Range) Return SUM(total travelling).

 

How would i go about doing that?

 

Kind Regards

 

Tom



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 06 May 2016 - 10:26 PM

Hi Taskmas7er,

 

This sounds like a situation that's ideally suited to a pivot table with slicers, and perhaps a Timeline to select the date range (although this can also be done with slicers).

 

You can see how to create a simple pivot table in the video lesson: Expert Skills Lesson 5-1 Create a pivot table.

You can see how to create slicers in the video tutorial: Expert Skills Lesson 5-8 Filter a pivot table visually using slicers.

Finally, you can see how to use timelines in: Expert Skills Lesson 5-9 Add a timeline control to a Pivot Table.

 

I have attached an example workbook showing how you might apply this to your needs:

 

Attached File  Travel.xlsx   17.16KB   59 downloads

Travel.png

 

I hope this is what you were looking for, but please feel free to reply if you need any more help with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users