Hi, I'm new here and badly need help. Stuck with this for 2 weeks now. Am absolute beginer so far using trial error methods to pass thru til this stage. Using Excel 2007 and Win7. Try my best to describe ...
I'm trying to retrieve Amount, Date & Remarks from the record list (Sheet2), based on Class in the Summary (Sheet1). However the info retrieved must match 4 criterias, and after which if there are more than 1 such info, the amount will be summed up.
The 4 criterias are;
1. Sheet2 B Column (Class) match with Sheet1 B Column (Class).
2. Sheet2 D Column (For Month) match with Sheet1 A1 cell.
3. Sheet2 E Column (Year) match with Sheet1 B1 cell.
4. Sheet2 F Column (Category) must be = a.
If there are more than one such data, the amount will be tallied up into respective's C column (Amt) in Sheet1. For Date (Col D) and Remarks (Col E) can they be combined as well? Or just from the latest record from Sheet1 will do.
I've managed to search through for the criteria using INDEX & MATCH, but unable to sum them up if there are more than 1 info. Can anyone help?? Thanks a billion!!!!
(Sheet1)Summary
A B C D E
1 Oct 2011
2 No. Class Amt Date Remarks
3 1 Class1
4 2 Class2
5 3 Class3
6 4 Class4
7 5 Class5
(Sheet2)Record List
A B C D E F G
1 Date Class For Month Year Category Amt Remarks
2 28/10/10 Class1 Oct 2010 1 $100 www
3 22/10/10 Class2 Oct 2010 1 $120 xxx
4 15/10/10 Class2 Oct 2010 1 $150 yyy
5 13/10/10 Class3 Oct 2010 1 $50 zzz
6 11/10/10 Class4 Oct 2010 1 $60 aaa
7 24/9/10 Class1 Oct 2010 1 $130 bbb
I'm trying to retrieve Amount, Date & Remarks from the record list (Sheet2), based on Class in the Summary (Sheet1). However the info retrieved must match 4 criterias, and after which if there are more than 1 such info, the amount will be summed up.
The 4 criterias are;
1. Sheet2 B Column (Class) match with Sheet1 B Column (Class).
2. Sheet2 D Column (For Month) match with Sheet1 A1 cell.
3. Sheet2 E Column (Year) match with Sheet1 B1 cell.
4. Sheet2 F Column (Category) must be = a.
If there are more than one such data, the amount will be tallied up into respective's C column (Amt) in Sheet1. For Date (Col D) and Remarks (Col E) can they be combined as well? Or just from the latest record from Sheet1 will do.
I've managed to search through for the criteria using INDEX & MATCH, but unable to sum them up if there are more than 1 info. Can anyone help?? Thanks a billion!!!!
(Sheet1)Summary
A B C D E
1 Oct 2011
2 No. Class Amt Date Remarks
3 1 Class1
4 2 Class2
5 3 Class3
6 4 Class4
7 5 Class5
(Sheet2)Record List
A B C D E F G
1 Date Class For Month Year Category Amt Remarks
2 28/10/10 Class1 Oct 2010 1 $100 www
3 22/10/10 Class2 Oct 2010 1 $120 xxx
4 15/10/10 Class2 Oct 2010 1 $150 yyy
5 13/10/10 Class3 Oct 2010 1 $50 zzz
6 11/10/10 Class4 Oct 2010 1 $60 aaa
7 24/9/10 Class1 Oct 2010 1 $130 bbb
