Formula - Sumif(s)? Sumproduct? etc.

[footoo]

I am looking for a formula to produce the results in column P.

XCash Summary.xlsm
	F	G	H	I	J	K	L	M	N	O	P	Q
1
2	8 Apr 25		HKD		1.0000		108,099
3	15 Oct 24		USD		1.0000		6,407
4	20 Jan 25		CNY		1.0000		535
5	14 Oct 24		HKD		1.0000		31,571
6	10 Mar 25		HKD				35,901
7	9 Dec 24		HKD		1.0000		42,575
8	27 Feb 25		HKD		1.0000		54,444
9	3 Jan 25		HKD		1.0000		31,095
10	17 Jan 25		HKD		1.0000		85,032
11	21 Jan 25		HKD		1.0000		51,438
12	18 Feb 25		HKD				86,394
13	21 Feb 25		HKD		1.0000		170,065
14	3 Oct 24		USD		1.0000		11,841
15	29 Aug 25		USD		1.0000		10,057
16	30 Jun 25		HKD		1.0000		96,652
17	30 Sep 24		USD		1.0000		55,892
18	20 Aug 25		USD		1.0000		14,348
19
20								55,892	24 09 USD		55,892 USD	Sep 24
21								31,571	24 10 HKD		31,571 HKD	Oct 24
22								18,248	24 10 USD		18,248 USD	Oct 24
23								42,575	24 12 HKD		42,575 HKD	Dec 24
24								535	25 01 CNY		535 CNY	Jan 25
25								167,565	25 01 HKD		167,565 HKD	Jan 25
26								224,509	25 02 HKD		224,509 HKD	Feb 25
27								108,099	25 04 HKD		108,099 HKD	Apr 25
28								96,652	25 06 HKD		96,652 HKD	Jun 25
29								24,405	25 08 USD		24,405 USD	Aug 25
Summary

Cell Formulas
Range	Formula
N20:N29	N20	=SORT(UNIQUE(FILTER(TEXT(F$1:F$19, "yy mm ")&H$1:H$19, J$1:J$19 <> "")))
Q20:Q29	Q20	=--SORT(LEFT((UNIQUE(FILTER(DATE(YEAR(F$1:F$19),MONTH(F$1:F$19),1)&H$1:H$19,J$1:J$19<>""))),5))
M20:M29	M20	=SUMPRODUCT((J$1:J$19<>"")*(TEXT(F$1:F$19,"yy mm ")&H$1:H$19=N20)*L$1:L$19)
Dynamic array formulas.

 

[footoo]

I spoke too soon. I'm still playing with it.

 

[Dave Patton]

Your edit of Fluff's formula can be edited to sort properly.

Did you try the Pivotby with the Filter Array parameter?

 

[footoo]

Since I can’t work out how to avoid adding columns, I have put this formula in T20 (first available blank helper column) :

=RIGHT(SORT(UNIQUE(FILTER(TEXT(F1:F19,"yy mm")&H1:H19,J1:J19 <> ""))),3)

Then this in N20 :

=--SORT(LEFT((UNIQUE(FILTER(DATE(YEAR($F$1:$F$19),MONTH($F$1:$F$19),1)&$H$1:$H$19,$J$1:$J$19<>""))),5))

And this in M20 :

=SUMPRODUCT(($J$1:$J$19<>"")*(DATE(YEAR($F$1:$F$19),MONTH($F$1:$F$19),1)=N20)*($H$1:$H$19=T20)*L$1:L$19)

The above works but I would still like to find a way to avoid the helper column.

 

[footoo]

Correction. The formula in M20 should be :

=TEXT(SUMPRODUCT(($J$1:$J$19<>"")*(DATE(YEAR($F$1:$F$19),MONTH($F$1:$F$19),1)=N20)*($H$1:$H$19=T20)*L$1:L$19),"#,##0 ")&T20

 

[Fluff]

How about

=LET(a,GROUPBY(FILTER(HSTACK(DATE(YEAR(F2:F18),MONTH(F2:F18),1),H2:H18),J2:J18<>""),FILTER(L2:L18,J2:J18<>""),SUM,,0),HSTACK(TEXT(INDEX(a,,3),"#,000")&" "&INDEX(a,,2),INDEX(a,,1)))

 

[footoo]

How about

=LET(a,GROUPBY(FILTER(HSTACK(DATE(YEAR(F2:F18),MONTH(F2:F18),1),H2:H18),J2:J18<>""),FILTER(L2:L18,J2:J18<>""),SUM,,0),HSTACK(TEXT(INDEX(a,,3),"#,000")&" "&INDEX(a,,2),INDEX(a,,1)))

That's great! Many thanks.

 

[Fluff]

Glad we could help & thanks for the feedback.