Dear Gulshan, will appreciate your help to help me on the couple of questions based on the attached picture
1. What formula can i use to generate how much a person spend in a particular year. e.g, for john, what formula can i insert in G2 (merge cell), such that excel can auto tabulate how much John spend in 2013 alone by adding the money he spend during the travel dates that fall in 2013 (cell C&D)
2. Given that there are more than 3 people in my list with the same format. how do i filter in such a way that i can find out who are the people that went to this particular country? for example, i want to know who are the people that went Australia. What can i do on another sheet such that excel can pull out for me, who are the people that went Australia. Please provide me with the method and the formula used.
Sincere apologies for the delayed response. Please see the answers below:
For Question 1:
1. Option 1 is that you can try to generate a Pivot table and filter it by year. Assuming you are using excel 2007 and above, it allows you to filter data by year.
2. Option 2 is that you can add a new column called year and link this formula "=year(a1)", this will give you the year for each expense item and then you can use "=sumif()" formula to add for individual people by individual years.
For Question 2:
For the second question, you can again go to the Pivot table and generate information by regions.
Hope this helps,
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here