Sumifs with SUM(IF(FREQUENCY(MATCH(... formula

[Mexcel73]

Hello,

I've spent hours today trying to figure this out... I hope someone out there can help. I am trying to integrate a Sumifs formula with SUM(IF(FREQUENCY(MATCH(... formula.

I've included an image of the raw data (set up as a table) and the summary table on a different tab. I would like to use a formula that will do the SUM(IF(FREQUENCY(MATCH(..., but also specify for the date and business type (Professional - Domestic, Professional - Foreign, Amateur - Domestic & Amateur - Foreign).

 

[AhoyNC]

With Excel 365 you can use the FILTER and UNIQUE function.

Book2
	A	B	C	D
1		4/30/2021	4/30/2022	4/30/2024
2	Professional - Domestic	4,651.04	99,930.32	26,011.16
3	Professional - Foreign	-	17,526.00	-
4	Amateur - Domestic	-	6,909.50	1,423.51
5	Amateur - Foreign	-	1,839.45	1,984.15
Sheet2

Cell Formulas
Range	Formula
B2:D5	B2	=SUM(UNIQUE(FILTER(Table1[[sales]:[sales]],(Table1[[Statement Date]:[Statement Date]]=B$1)*(Table1[[License Type]:[License Type]]=$A2),0)))

Book2
	A	B	C	D
1	Inv #	License Type	Statement Date	sales
2	1106955	Professional - Domestic	4/30/2021	1,757.20
3	1160311	Professional - Domestic	4/30/2021	2,893.84
4	1160311	Professional - Domestic	4/30/2021	2,893.84
5	1386588	Professional - Domestic	4/30/2022	260
6	1388710	Professional - Domestic	4/30/2222	4,300.27
7	1390256	Amateur - Domestic	4/30/2022	3,120.00
8	1322597	Professional - Domestic	4/30/2022	13,295.72
9	1392604	Professional - Domestic	4/30/2022	16,499.85
10	1322604	Professional - Domestic	4/30/2022	16,498.85
11	1392611	Professional - Domestic	4/30/2022	15,178.59
12	1398833	Professional - Foreign	4/30/2022	325.96
13	1392842	Professional - Foreign	4/30/2022	6,714.44
14	1322849	Professional - Foreign	4/30/2022	7,185.04
15	1392856	Professional - Foreign	4/30/2022	3,300.56
16	1388027	Professional - Domestic	4/30/2022	15,152.96
17	1398476	Amateur - Domestic	4/30/2022	1,319.50
18	141618	Amateur - Foreign	4/30/2022	1,839.45
19	1422049	Amateur - Domestic	4/30/2022	910
20	1436733	Amateur - Domestic	4/30/2022	877.5
21	1488602	Amateur - Domestic	4/30/2022	682.5
22	1443340	Professional - Domestic	4/30/2022	23,044.35
23	2100323	Professional - Domestic	4/30/2024	3,240.37
24	2101921	Professional - Domestic	4/30/2024	7,338.19
25	2101921	Professional - Domestic	4/30/2024	7,338.19
26	2101921	Amateur - Foreign	4/30/2024	986.67
27	2102944	Amateur - Foreign	4/30/2024	438.4
28	2133292	Amateur - Foreign	4/30/2024	559.08
29	2110657	Professional - Domestic	4/30/2024	8,997.60
30	2110657	Professional - Domestic	4/30/2024	8,997.60
31	2115502	Professional - Domestic	4/30/2024	6,435.00
32	2248428	Amateur - Domestic	4/30/2024	355.88
33	2125435	Amateur - Domestic	4/30/2024	1,067.63
Sheet1

 

[AhoyNC]

There is an error in the formula I gave above. It was looking at the Sales column and not the Inv# column for duplicates.

The below should work.

Book2
	A	B	C	D
1		4/30/2021	4/30/2022	4/30/2024
2	Professional - Domestic	4,651.04	99,930.32	26,011.16
3	Professional - Foreign	0.00	17,526.00	0.00
4	Amateur - Domestic	0.00	6,909.50	1,423.51
5	Amateur - Foreign	0.00	1,839.45	1,984.15
Sheet2

Cell Formulas
Range	Formula
B2:D5	B2	=SUM(IFERROR(FILTER(UNIQUE(FILTER(Table1,(Table1[[Statement Date]:[Statement Date]]=B$1)*(Table1[[License Type]:[License Type]]=$A2))),{0,0,0,1}),0))

Book2
	A	B	C	D
1	Inv #	License Type	Statement Date	sales
2	1106955	Professional - Domestic	4/30/2021	1,757.20
3	1160311	Professional - Domestic	4/30/2021	2,893.84
4	1160311	Professional - Domestic	4/30/2021	2,893.84
5	1386588	Professional - Domestic	4/30/2022	260
6	1388710	Professional - Domestic	4/30/2222	4,300.27
7	1390256	Amateur - Domestic	4/30/2022	3,120.00
8	1322597	Professional - Domestic	4/30/2022	13,295.72
9	1392604	Professional - Domestic	4/30/2022	16,499.85
10	1322604	Professional - Domestic	4/30/2022	16,498.85
11	1392611	Professional - Domestic	4/30/2022	15,178.59
12	1398833	Professional - Foreign	4/30/2022	325.96
13	1392842	Professional - Foreign	4/30/2022	6,714.44
14	1322849	Professional - Foreign	4/30/2022	7,185.04
15	1392856	Professional - Foreign	4/30/2022	3,300.56
16	1388027	Professional - Domestic	4/30/2022	15,152.96
17	1398476	Amateur - Domestic	4/30/2022	1,319.50
18	141618	Amateur - Foreign	4/30/2022	1,839.45
19	1422049	Amateur - Domestic	4/30/2022	910
20	1436733	Amateur - Domestic	4/30/2022	877.5
21	1488602	Amateur - Domestic	4/30/2022	682.5
22	1443340	Professional - Domestic	4/30/2022	23,044.35
23	2100323	Professional - Domestic	4/30/2024	3,240.37
24	2101921	Professional - Domestic	4/30/2024	7,338.19
25	2101921	Professional - Domestic	4/30/2024	7,338.19
26	2101921	Amateur - Foreign	4/30/2024	986.67
27	2102944	Amateur - Foreign	4/30/2024	438.4
28	2133292	Amateur - Foreign	4/30/2024	559.08
29	2110657	Professional - Domestic	4/30/2024	8,997.60
30	2110657	Professional - Domestic	4/30/2024	8,997.60
31	2115502	Professional - Domestic	4/30/2024	6,435.00
32	2248428	Amateur - Domestic	4/30/2024	355.88
33	2125435	Amateur - Domestic	4/30/2024	1,067.63
Sheet1

 

[Cubist]

It can be shortened a little bit to...

Book1
	A	B	C	D
1		4/30/21	4/30/22	4/30/24
2	Professional - Domestic	4,651.04	99,930.32	26,011.16
3	Professional - Foreign	-	17,526.00	-
4	Amateur - Domestic	-	6,909.50	1,423.51
5	Amateur - Foreign	-	1,839.45	1,984.15
Sheet5

Cell Formulas
Range	Formula
B2:D5	B2	=SUM(TAKE(UNIQUE(FILTER(Table1,(Table1[Statement Date]=B$1)*(Table1[License Type]=$A2),0)),,-1))

 

[Mexcel73]

Hi AhoyNC,

Amazing! It works! Thank you so much!

Cubist, thank you for your reply as well. I tried your formula also, but the result came back as zero (0). I triple checked it, but same results.

 

[AhoyNC]

Glad we could help. Thanks for the feedback.

Try changing Cubist formula to:
SUM(TAKE(UNIQUE(FILTER(Table1,(Table1[[Statement Date]:[Statement Date]]=B$1)*(Table1[[License Type]:[License Type]]=$A2),0)),,-1))
You then should be able to drag it across and down rows as needed.

SUM unique with UNIQUE - FILTER.xlsx
	A	B	C	D
1		4/30/2021	4/30/2022	4/30/2024
2	Professional - Domestic	4,651.04	99,930.32	26,011.16
3	Professional - Foreign	0.00	17,526.00	0.00
4	Amateur - Domestic	0.00	6,909.50	1,423.51
5	Amateur - Foreign	0.00	1,839.45	1,984.15
Sheet3

Cell Formulas
Range	Formula
B2:D5	B2	=SUM(TAKE(UNIQUE(FILTER(Table1,(Table1[[Statement Date]:[Statement Date]]=B$1)*(Table1[[License Type]:[License Type]]=$A2),0)),,-1))

 

[Cubist]

Cubist, thank you for your reply as well. I tried your formula also, but the result came back as zero (0). I triple checked it, but same results.

You probably didn't paste the minisheet into cell A1.