database_sumprod_sumifs.xlsx

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Comparison of SUMPRODUCT, SUMIFS and DSUM
2	Duncan Williamson: Excel Master from excelmaster.co
3	25th March 2010 revised 11th April 2019 and 12th December 2024
4
5	Region	City	Chain	Product	Total Sales
6	North GA	Atlanta	Fruit R Us	Oranges	61,650
7	North GA	Atlanta	Fruit R Us	Apples	85,106
8	North GA	Atlanta	Fruit R Us	Bananas	75,548
9	North GA	Atlanta	Bob's Fruit	Oranges	93,816
10	North GA	Atlanta	Bob's Fruit	Apples	21,910
11	North GA	Atlanta	Bob's Fruit	Bananas	98,420
12	North GA	Blue Ridge	Mountain Fruit	Oranges	89,810
13	North GA	Blue Ridge	Mountain Fruit	Apples	83,538
14	North GA	Blue Ridge	Mountain Fruit	Bananas	60,900
15	North GA	Clarkesville	Fruit Direct	Oranges	9,604
16	North GA	Clarkesville	Fruit Direct	Apples	82,030
17	North GA	Clarkesville	Middle Georgia Fruit	Bananas	107,406
18	Mid GA	Macon	Middle Georgia Fruit	Oranges	71,097
19	Mid GA	Macon	Middle Georgia Fruit	Apples	15,764
20	Mid GA	Macon	Whistlestop Fruit Stand	Bananas	14,730
21	Mid GA	Macon	Whistlestop Fruit Stand	Oranges	149,745
22	Mid GA	Macon	Whistlestop Fruit Stand	Apples	108,147
23	Mid GA	Macon	Whistlestop Fruit Stand	Bananas	87,934
24
25						Change one or more of the variables you want to test for
26						Product	Region	City	Chain
27						Apples	North GA	Atlanta	Fruit R Us
28											SUMPRODUCT	SUMIFS	DAVERAGE
29										Product	396,495	396,495	66,083	=DAVERAGE(A$5:E$23,E$5,F26:F27)
30										Product and Region	272,584	272,584	68,146	=DAVERAGE(A$5:E$23,E$5,F26:G27)
31										Product, Region and City	107,016	107,016	53,508	=DAVERAGE(A$5:E$23,E$5,F26:H27)
32										Product, Region, City and Chain	85,106	85,106	85,106	=DAVERAGE(A$5:E$23,E$5,F26:I27)
33
34											=ARRAY_CONSTRAIN(ARRAYFORMULA(SUMPRODUCT((D6:D23=F27)*E6:E23)), 1, 1)	=SUMIFS(E6:E23,D6:D23,F27)
35											=SUMPRODUCT((A6:A23=G27)(D6:D23=F27)E6:E23)	=SUMIFS(E6:E23,A6:A23,G27,D6:D23,F27)
36											=SUMPRODUCT((A6:A23=G27)(B6:B23=H27)(D6:D23=F27)*E6:E23)	=SUMIFS(E6:E23,A6:A23,G27,B6:B23,H27,D6:D23,F27)
37											=SUMPRODUCT((A6:A23=G27)(B6:B23=H27)(C6:C23=I27)(D6:D23=F27)E6:E23)	=SUMIFS(E6:E23,A6:A23,G27,B6:B23,H27,C6:C23,I27,D6:D23,F27)
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100