Combining IF, SORT and UNIQUE

[kumara_faith]

Hi,

I have the following input data :

Expense Tracker.xlsx
	B	C	D	E
7	Date	Expense	Amount (INR)	Amount (RM)
8	Sunday, 30 October, 2022	Biscuit	200	11
9	Sunday, 30 October, 2022	Biscuit	250	14
10	Monday, 31 October, 2022	Book	150	9
11	Tuesday, 1 November, 2022	Book	100	6
12	Thursday, 3 November, 2022	Breakfast	200	11
Expense Journal

Cell Formulas
Range	Formula
E8:E12	E8	=IF(D8="","",D8/$E$2)

Cells with Data Validation
Cell	Allow	Criteria
B8:B356	List	=Date
C8:C470	List	=Expense1

In another table, I am trying to summarize the data based on expense type as follows (sample of correct results). The expenses are grouped and is in order of highest to lowest expense type.

Expense Tracker.xlsx
	G	H	I
7	Expense	Amount(INR)	Amount(MYR)
8	Biscuit	450	26
9	Book	300	14
10	Breakfast	200	11
Expense Journal

I tried using SORT function but I could not get it right. Appreciate any help:

Expense Tracker.xlsx
	G	H	I
15	Expense	Amount(INR)	Amount(MYR)
16	Biscuit	250	14
17	Biscuit	200	11
18	Breakfast	200	11
19	Book	150	9
20	Book	100	6
Expense Journal

Cell Formulas
Range	Formula
G16:I20	G16	=SORT(C8:E12,2,-1)
Dynamic array formulas.

 

[Fluff]

How about

Fluff.xlsm
	B	C	D	E
7	Date	Expense	Amount (INR)	Amount (RM)
8	44864	Biscuit	200	11
9	44864	Biscuit	250	14
10	44865	Book	150	9
11	44866	Book	100	6
12	44868	Breakfast	200	11
13
14
15
16
17	Biscuit	450	25
18	Book	250	15
19	Breakfast	200	11
Update

Cell Formulas
Range	Formula
B17:D19	B17	=LET(u,UNIQUE(C8:C12),SORT(HSTACK(u,SUMIFS(D8:D12,C8:C12,u),SUMIFS(E8:E12,C8:C12,u)),2,-1))
Dynamic array formulas.

 

[kumara_faith]

Hi Fluff,

Thank you for your solution and that worked. Appreciate your assistance and have a great day ahead.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[kumara_faith]

Hi Fluff,

Sorry. This is my actual table in sheet 1:

Expense Tracker.xlsx
	B	C	D	E
7	Date	Expense	Amount (INR)	Amount (RM)
8	Sunday, 30 October, 2022	Accommodation	50	3
9	Sunday, 30 October, 2022	Biscuit	50	3
10	Monday, 31 October, 2022	Biscuit	51	3
11
12
13
Expense Journal

Cell Formulas
Range	Formula
E8:E13	E8	=IF(D8="","",D8/$E$2)

Cells with Data Validation
Cell	Allow	Criteria
B8:B356	List	=Date
C8:C470	List	=Expense1

And this is the formula in sheet2:

Expense Tracker.xlsx
	B	C	D
2	Expense Category	Amount RM	Amount INR
3	Biscuit	101	6
4	Accommodation	50	3
5	0	0	0
Summary

Cell Formulas
Range	Formula
B3:D5	B3	=LET(u,UNIQUE('Expense Journal'!C8:C190),SORT(HSTACK(u,SUMIFS('Expense Journal'!D8:D190,'Expense Journal'!C8:C190,u),SUMIFS('Expense Journal'!E8:E190,'Expense Journal'!C8:C190,u)),2,-1))
Dynamic array formulas.

Is there a reason why the last row after last data is always zero ? In this case, it will be row 5 ? Thanks

 

[Fluff]

You need to filter out the blank rows.

=LET(u,UNIQUE(FILTER('Expense Journal'!C8:C190,'Expense Journal'!C8:C190<>"")),SORT(HSTACK(u,SUMIFS('Expense Journal'!D8:D190,'Expense Journal'!C8:C190,u),SUMIFS('Expense Journal'!E8:E190,'Expense Journal'!C8:C190,u)),2,-1))

 

[kumara_faith]

Thank you Fluff. That worked. Appreciate your assistance and have a wonderful day ahead.

 

[Fluff]

Glad to help & thanks for the feedback.