case example
case example  
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.

Thanks alot

Hello Chin,

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,
About Excel
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


All Answers

Answers by Expert:

Ask Experts




I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.


I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2016 About.com. All rights reserved.