kumara_faith
Well-known Member
- Joined
- Aug 19, 2006
- Messages
- 951
- Office Version
- 365
Hi,
I have the following input table:
On another sheet, I am trying to summarize the expenses based on category and by highest amount to lowest automatically as the expenses details are being entered:
However, as per the table above, the amount is not sorted by highest to lowest automatically. Is there a way to accomplish this ?
I have the following input table:
Expense Tracker_Test.xlsx | |||||
---|---|---|---|---|---|
B | C | D | |||
7 | Date | Expense | Amount (INR) | ||
8 | Saturday, 29 October, 2022 | Incense | 500 | ||
9 | Sunday, 30 October, 2022 | Biscuit | 300 | ||
10 | Sunday, 30 October, 2022 | Book | 200 | ||
11 | Tuesday, 1 November, 2022 | Breakfast | 100 | ||
12 | Wednesday, 2 November, 2022 | Internet | 200 | ||
13 | Saturday, 5 November, 2022 | Donation | 600 | ||
Expense Journal |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
B8:B356 | List | =Date |
C8:C470 | List | =Expense1 |
On another sheet, I am trying to summarize the expenses based on category and by highest amount to lowest automatically as the expenses details are being entered:
Expense Tracker_Test.xlsx | |||||
---|---|---|---|---|---|
B | C | D | |||
2 | Expense Category | Amount RM | Amount INR | ||
3 | Biscuit | 300 | 17 | ||
4 | Book | 200 | 11 | ||
5 | Breakfast | 100 | 6 | ||
6 | Donation | 600 | 34 | ||
7 | Incense | 500 | 28 | ||
8 | Internet | 200 | 11 | ||
Summary |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B3:B8 | B3 | =SORT(UNIQUE(FILTER('Expense Journal'!C8:C362,'Expense Journal'!C8:C362<>""))) |
C3:C8 | C3 | =IF(B3=0,"",SUMIF('Expense Journal'!$C$8:$C$350,Summary!B3,'Expense Journal'!$D$8:$D$350)) |
D3:D8 | D3 | =IF(B3=0,"",SUMIF('Expense Journal'!$C$8:$C$350,Summary!B3,'Expense Journal'!$E$8:$E$350)) |
Dynamic array formulas. |
However, as per the table above, the amount is not sorted by highest to lowest automatically. Is there a way to accomplish this ?