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.

 

[Mexcel73]

This has been resolved. Not sure of how to close out a threat/post here.

 

[Peter_SSs]

This has been resolved. Not sure of how to close out a threat/post here.

If one of the above posts solved your problem then click the 'Mark as solution' option at the right of that post.

If there was some other way that you resolved it then do not mark any post as the solution and your comment in post 8 that it is resolved is sufficient.