Sumifs with index match match inquiry (could be Sumproduct related)

[ExcelUser18]

Hi,

I have two-pronged question though the most important one will be the 1st bullet and the less important will be the 2nd bullet. The uploaded excel will show the same format for the aggregate data on the top and the different sets of data outlined in black below it. Unsure if sumifs and index match match would be appropriate or if it would be a sumproduct with various arrays.

• Highlighted in the orange row, I am looking for sumifs formula where I'd reference an entire section of data (outlined in the box from cell C16:Q39) that would match the Identifying Category in Column C and the respective Date in Row 8 to the outlined box. In the uploaded excel, highlighted in orange, I have the result which is much bulkier and less efficient than what I believe can be done.
• Highlighted in Yellow, I'd like to create a minif with only 1 criterion. Right now I have 3 different sets of data in the outlined box but if I added a 4th, I'd like cell D8 to search the larger outlined box to find me the earliest start data. Right now it shows 3/16/20 but should I add another set of data that started 11/1/2019, I'd like Row 8 to update. Excel only allows MinIFS with multiple criteria, however, I'd only have one criterion which would be the Identifying Category of "Date".

Any help is appreciated. I've searched different threads but it did not show the match/match component I think I'm looking for that's reference in my bulleted example above.

	Year	0	1	1	1	1	1	1	1	1	1	1	1	1	2	2	2	2	2	2	2	2	2
	Month	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21
	Date	3/16/2020	4/30/2020	5/31/2020	6/30/2020	7/31/2020	8/31/2020	9/30/2020	10/31/2020	11/30/2020	12/31/2020	1/31/2021	2/28/2021	3/31/2021	4/30/2021	5/31/2021	6/30/2021	7/31/2021	8/31/2021	9/30/2021	10/31/2021	11/30/2021	12/31/2021
	CF	-30,000.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	232.50	213.55	0.00	0.00	106.05	0.00	0.00	143.98	0.00	0.00
	Refi/Sale	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	10,010.00	0.00	0.00	0.00	0.00	0.00	10,226.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
	Total	-30,000.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	10,010.00	0.00	0.00	0.00	232.50	213.55	10,226.00	0.00	106.05	0.00	0.00	143.98	0.00	0.00
	Year	0	1	1	1	1	1	1	1	1	1	1	1	1	2
	Month	0	1	2	3	4	5	6	7	8	9	10	11	12	13
	Date	3/16/2020	4/30/2020	5/31/2020	6/30/2020	7/31/2020	8/31/2020	9/30/2020	10/31/2020	11/30/2020	12/31/2020	1/31/2021	2/28/2021	3/31/2021	4/30/2021
	CF	-30,000.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	232.50	0.00
	Refi/Sale	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	10,010.00	0.00	0.00	0.00	0.00	0.00
	Total	-30,000.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	10,010.00	0.00	0.00	0.00	232.50	0.00
	Year	0	1	1	1	1	1	1	1	1	1	1	1	1	2
	Month	0	1	2	3	4	5	6	7	8	9	10	11	12	13
	Date	12/24/2020	1/31/2021	2/28/2021	3/31/2021	4/30/2021	5/31/2021	6/30/2021	7/31/2021	8/31/2021	9/30/2021	10/31/2021	11/30/2021	12/31/2021	1/31/2022
	CF	-30,000.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
	Refi/Sale	0.00	0.00	0.00	0.00	0.00	10,226.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
	Total	-30,000.00	0.00	0.00	0.00	0.00	10,226.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
	Year	0	1	1	1	1	1	1	1	1	1	1	1	1	2
	Month	0	1	2	3	4	5	6	7	8	9	10	11	12	13
	Date	11/1/2020	12/31/2020	1/31/2021	2/28/2021	3/31/2021	4/30/2021	5/31/2021	6/30/2021	7/31/2021	8/31/2021	9/30/2021	10/31/2021	11/30/2021	12/31/2021
	CF	-10,000.00	0.00	0.00	0.00	0.00	213.55	0.00	0.00	106.05	0.00	0.00	143.98	0.00	0.00
	Refi/Sale	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
	Total	-10,000.00	0.00	0.00	0.00	0.00	213.55	0.00	0.00	106.05	0.00	0.00	143.98	0.00	0.00

 

[ExcelUser18]

Sure thing, see below. The last box at the bottom, which has a start date of 11/15/2021, is where I'm having the issues. When I scroll up to the top and try to alter your formulas to drag down and include the last box, I get the error. The current formula does not have the error.

Book1
	I	J	K	L	M	N	O	P	Q
1	Year		0	1	1	1	1	1	1
2	Month		0	1	2	3	4	5	6
3	Date	3/16/2020	3/31/2020	4/30/2020	5/31/2020	6/30/2020	7/31/2020	8/31/2020	9/30/2020
4	Initial		$ (30,000.00)	$ -	$ -	$ -	$ -	$ -	$ -
5	CF		$ -	$ -	$ -	$ -	$ -	$ -	$ -
6	Refinance		$ -	$ -	$ -	$ -	$ -	$ -	$ -
7	Sale		$ -	$ -	$ -	$ -	$ -	$ -	$ -
8	Total		$ (30,000.00)	$ -	$ -	$ -	$ -	$ -	$ -
9
10	Basis			30,000.00	30,000.00	30,000.00	30,000.00	30,000.00	30,000.00
11	CoC - Initial			0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
12	CoC - Basis			0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
13	IRR		5.3%
14	EM		1.19
15
16
17
18
19
20	Year		0	1	1	1	1	1	1
21	Month		0	1	2	3	4	5	6
22	Date	3/16/2020	3/31/2020	4/30/2020	5/31/2020	6/30/2020	7/31/2020	8/31/2020	9/30/2020
23	Initial		$ (30,000.00)	$ -	$ -	$ -	$ -	$ -	$ -
24	CF		$ -	$ -	$ -	$ -	$ -	$ -	$ -
25	Refinance		$ -	$ -	$ -	$ -	$ -	$ -	$ -
26	Sale		$ -	$ -	$ -	$ -	$ -	$ -	$ -
27	Total		$ (30,000.00)	$ -	$ -	$ -	$ -	$ -	$ -
28				0.0%	0.0%	0.0%	0.0%	0.0%	0.0%
29	Basis			30,000.00	30,000.00	30,000.00	30,000.00	30,000.00	30,000.00
30	CoC - Initial			0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
31	CoC - Basis			0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
32	IRR		19.6%
33	EM		1.76
34
35
36
37
38
39
40	Year		0	1	1	1	1	1	1
41	Month		0	1	2	3	4	5	6
42	Date	12/24/2020	12/31/2020	1/31/2021	2/28/2021	3/31/2021	4/30/2021	5/31/2021	6/30/2021
43	Initial		$ (30,000.00)	$ -	$ -	$ -	$ -	$ -	$ -
44	CF		$ -	$ -	$ -	$ -	$ -	$ -	$ 180.72
45	Refinance		$ -	$ -	$ -	$ -	$ -	$ 10,226.00	$ -
46	Sale		$ -	$ -	$ -	$ -	$ -	$ -	$ -
47	Total		$ (30,000.00)	$ -	$ -	$ -	$ -	$ 10,226.00	$ 180.72
48				0.0%	0.0%	0.0%	0.0%	34.1%	0.6%
49	Basis			30,000.00	30,000.00	30,000.00	30,000.00	19,774.00	19,593.28
50	CoC - Initial			0.00%	0.00%	0.00%	0.00%	0.00%	0.60%
51	CoC - Basis			0.00%	0.00%	0.00%	0.00%	0.00%	0.92%
52	IRR		27.3%
53	EM		2.11
54
55
56
57
58
59
60	Year		0	1	1	1	1	1	1
61	Month		0	1	2	3	4	5	6
62	Date	11/1/2020	11/30/2020	12/31/2020	1/31/2021	2/28/2021	3/31/2021	4/30/2021	5/31/2021
63	Initial		$ (10,000.00)	$ -	$ -	$ -	$ -	$ -	$ -
64	CF		$ -	$ -	$ -	$ -	$ -	$ 213.55	$ -
65	Refinance		$ -	$ -	$ -	$ -	$ -	$ -	$ -
66	Sale		$ -	$ -	$ -	$ -	$ -	$ -	$ -
67	Total		$ (10,000.00)	$ -	$ -	$ -	$ -	$ 213.55	$ -
68				0.0%	0.0%	0.0%	0.0%	2.1%	0.0%
69	Basis			10,000.00	10,000.00	10,000.00	10,000.00	9,786.45	9,786.45
70	CoC - Initial			0.00%	0.00%	0.00%	0.00%	2.14%	0.00%
71	CoC - Basis			0.00%	0.00%	0.00%	0.00%	2.18%	0.00%
72	IRR		-8.6%
73	EM		0.71
74
75
76
77
78
79
80	Year		0	1	1	1	1	1	1
81	Month		0	1	2	3	4	5	6
82	Date	3/29/2021	3/31/2021	4/30/2021	5/31/2021	6/30/2021	7/31/2021	8/31/2021	9/30/2021
83	Initial		$ (10,000.00)	$ -	$ -	$ -	$ -	$ -	$ -
84	CF		$ -	$ -	$ -	$ -	$ 359.37	$ -	$ -
85	Refinance		$ -	$ -	$ -	$ -	$ -	$ -	$ -
86	Sale		$ -	$ -	$ -	$ -	$ -	$ -	$ -
87	Total		$ (10,000.00)	$ -	$ -	$ -	$ 359.37	$ -	$ -
88				0.0%	0.0%	0.0%	3.6%	0.0%	0.0%
89	Basis			10,000.00	10,000.00	10,000.00	9,640.63	9,640.63	9,640.63
90	CoC - Initial			0.00%	0.00%	0.00%	3.59%	0.00%	0.00%
91	CoC - Basis			0.00%	0.00%	0.00%	3.73%	0.00%	0.00%
92	IRR		16.3%
93	EM		1.76
94
95
96
97
98
99
100	Year		0	1	1	1	1	1	1
101	Month		0	1	2	3	4	5	6
102	Date	7/7/2021	7/31/2021	8/31/2021	9/30/2021	10/31/2021	11/30/2021	12/31/2021	1/31/2022
103	Initial		$ (40,000.00)	$ -	$ -	$ -	$ -	$ -	$ -
104	CF		$ -	$ -	$ -	$ -	$ -	$ -	$ -
105	Refinance		$ -	$ -	$ -	$ -	$ -	$ -	$ -
106	Sale		$ -	$ -	$ -	$ -	$ -	$ -	$ -
107	Total		$ (40,000.00)	$ -	$ -	$ -	$ -	$ -	$ -
108				0.0%	0.0%	0.0%	0.0%	0.0%	0.0%
109	Basis			40,000.00	40,000.00	40,000.00	40,000.00	40,000.00	40,000.00
110	CoC - Initial			0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
111	CoC - Basis			0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
112	IRR		#NUM!
113	EM		0.00
114
115
116
117
118
119
120	Year		0	1	1	1	1	1	1
121	Month		0	1	2	3	4	5	6
122	Date	11/15/2021	11/30/2021	12/31/2021	1/31/2022	2/28/2022	3/31/2022	4/30/2022	5/31/2022
123	Initial		$ (40,000.00)	$ -	$ -	$ -	$ -	$ -	$ -
124	CF		$ -	$ -	$ -	$ -	$ -	$ -	$ -
125	Refinance		$ -	$ -	$ -	$ -	$ -	$ -	$ -
126	Sale		$ -	$ -	$ -	$ -	$ -	$ -	$ -
127	Total		$ (40,000.00)	$ -	$ -	$ -	$ -	$ -	$ -
128				0.0%	0.0%	0.0%	0.0%	0.0%	0.0%
129	Basis			40,000.00	40,000.00	40,000.00	40,000.00	40,000.00	40,000.00
130	CoC - Initial			0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
131	CoC - Basis			0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
132	IRR		#NUM!
133	EM		0.00
134
CRE

Cell Formulas
Range	Formula
K120:Q120,K100:Q100,K80:Q80,K60:Q60,K40:Q40,K20:Q20,K1:Q1	K1	=ROUNDUP(K2/12,0)
L2:Q2,L121:Q121,L101:Q101,L81:Q81,L61:Q61,L41:Q41,L21:Q21	L2	=K2+1
J3	J3	=MINIFS(J$22:J$1005,I$22:I$1005,"date")
K3,K122,K102,K82,K62,K42,K22	K3	=EOMONTH(J3,0)
L3:Q3,L122:Q122,L102:Q102,L82:Q82,L62:Q62,L42:Q42,L22:Q22	L3	=EOMONTH(K3,1)
K4:Q7	K4	=SUMPRODUCT(($I23:$I103=$I4)*($K$22:$BG$102=K$3),$K23:$BG103)
K8:Q8,K127:Q127,K107:Q107,K87:Q87,K67:Q67,K47:Q47,K27:Q27	K8	=SUM(K4:K7)
L10:Q10,L129:Q129,L109:Q109,L89:Q89,L69:Q69,L49:Q49,L29:Q29	L10	=IF(-SUM($K8:L8)<0,0,-SUM($K8:L8))
L11:Q11,L130:Q130,L110:Q110,L90:Q90,L70:Q70,L50:Q50,L30:Q30	L11	=L5/-SUMIF($K4:$BG4,"<0")
L12:Q12,L131:Q131,L111:Q111,L91:Q91,L71:Q71,L51:Q51,L31:Q31	L12	=IF(L10=0,"Infinity",L5/L10)
K13	K13	=XIRR(K8:CQ8,K3:CQ3)
K14	K14	=SUMIF(K8:CQ8,">0")/-SUMIF(K8:CQ8,"<0")
K23,K123,K103,K83,K63,K43	K23	=-D29
L28:Q28,L128:Q128,L108:Q108,L88:Q88,L68:Q68,L48:Q48	L28	=L27/-$K27
K32,K132,K112,K92,K72,K52	K32	=XIRR(K27:BV27,K22:BV22)
K33,K133,K113,K93,K73,K53	K33	=SUMIF(K27:BV27,">0")/-SUMIF(K27:BV27,"<0")
P45	P45	=D51

 

[ExcelUser18]

Here it is with the error....

Book1
	I	J	K	L	M	N
1	Year		0	1	1	1
2	Month		0	1	2	3
3	Date	3/16/2020	3/31/2020	4/30/2020	5/31/2020	6/30/2020
4	Initial		#NUM!	$ -	$ -	$ -
5	CF		#NUM!	$ -	$ -	$ -
6	Refinance		#NUM!	$ -	$ -	$ -
7	Sale		#NUM!	$ -	$ -	$ -
8	Total		#NUM!	$ -	$ -	$ -
9
10	Basis			#NUM!	#NUM!	#NUM!
11	CoC - Initial			0.00%	0.00%	0.00%
12	CoC - Basis			#NUM!	#NUM!	#NUM!
13	IRR		#NUM!
14	EM		1.64
15
16
17
18
19
20	Year		0	1	1	1
21	Month		0	1	2	3
22	Date	3/16/2020	3/31/2020	4/30/2020	5/31/2020	6/30/2020
23	Initial		$ (30,000.00)	$ -	$ -	$ -
24	CF		$ -	$ -	$ -	$ -
25	Refinance		$ -	$ -	$ -	$ -
26	Sale		$ -	$ -	$ -	$ -
27	Total		$ (30,000.00)	$ -	$ -	$ -
28				0.0%	0.0%	0.0%
29	Basis			30,000.00	30,000.00	30,000.00
30	CoC - Initial			0.00%	0.00%	0.00%
31	CoC - Basis			0.00%	0.00%	0.00%
32	IRR		19.6%
33	EM		1.76
34
35
36
37
38
39
40	Year		0	1	1	1
41	Month		0	1	2	3
42	Date	12/24/2020	12/31/2020	1/31/2021	2/28/2021	3/31/2021
43	Initial		$ (30,000.00)	$ -	$ -	$ -
44	CF		$ -	$ -	$ -	$ -
45	Refinance		$ -	$ -	$ -	$ -
46	Sale		$ -	$ -	$ -	$ -
47	Total		$ (30,000.00)	$ -	$ -	$ -
48				0.0%	0.0%	0.0%
49	Basis			30,000.00	30,000.00	30,000.00
50	CoC - Initial			0.00%	0.00%	0.00%
51	CoC - Basis			0.00%	0.00%	0.00%
52	IRR		27.3%
53	EM		2.11
54
55
56
57
58
59
60	Year		0	1	1	1
61	Month		0	1	2	3
62	Date	11/1/2020	11/30/2020	12/31/2020	1/31/2021	2/28/2021
63	Initial		$ (10,000.00)	$ -	$ -	$ -
64	CF		$ -	$ -	$ -	$ -
65	Refinance		$ -	$ -	$ -	$ -
66	Sale		$ -	$ -	$ -	$ -
67	Total		$ (10,000.00)	$ -	$ -	$ -
68				0.0%	0.0%	0.0%
69	Basis			10,000.00	10,000.00	10,000.00
70	CoC - Initial			0.00%	0.00%	0.00%
71	CoC - Basis			0.00%	0.00%	0.00%
72	IRR		-8.6%
73	EM		0.71
74
75
76
77
78
79
80	Year		0	1	1	1
81	Month		0	1	2	3
82	Date	3/29/2021	3/31/2021	4/30/2021	5/31/2021	6/30/2021
83	Initial		$ (10,000.00)	$ -	$ -	$ -
84	CF		$ -	$ -	$ -	$ -
85	Refinance		$ -	$ -	$ -	$ -
86	Sale		$ -	$ -	$ -	$ -
87	Total		$ (10,000.00)	$ -	$ -	$ -
88				0.0%	0.0%	0.0%
89	Basis			10,000.00	10,000.00	10,000.00
90	CoC - Initial			0.00%	0.00%	0.00%
91	CoC - Basis			0.00%	0.00%	0.00%
92	IRR		16.3%
93	EM		1.76
94
95
96
97
98
99
100	Year		0	1	1	1
101	Month		0	1	2	3
102	Date	7/7/2021	7/31/2021	8/31/2021	9/30/2021	10/31/2021
103	Initial		$ (40,000.00)	$ -	$ -	$ -
104	CF		$ -	$ -	$ -	$ -
105	Refinance		$ -	$ -	$ -	$ -
106	Sale		$ -	$ -	$ -	$ -
107	Total		$ (40,000.00)	$ -	$ -	$ -
108				0.0%	0.0%	0.0%
109	Basis			40,000.00	40,000.00	40,000.00
110	CoC - Initial			0.00%	0.00%	0.00%
111	CoC - Basis			0.00%	0.00%	0.00%
112	IRR		#NUM!
113	EM		0.00
114
115
116
117
118
119
120	Year		0	1	1	1
121	Month		0	1	2	3
122	Date	11/15/2021	11/30/2021	12/31/2021	1/31/2022	2/28/2022
123	Initial		$ (40,000.00)	$ -	$ -	$ -
124	CF		$ -	$ -	$ -	$ -
125	Refinance		$ -	$ -	$ -	$ -
126	Sale		$ -	$ -	$ -	$ -
127	Total		$ (40,000.00)	$ -	$ -	$ -
128				0.0%	0.0%	0.0%
129	Basis			40,000.00	40,000.00	40,000.00
130	CoC - Initial			0.00%	0.00%	0.00%
131	CoC - Basis			0.00%	0.00%	0.00%
132	IRR		#NUM!
133	EM		0.00
134
CRE

Cell Formulas
Range	Formula
K120:N120,K100:N100,K80:N80,K60:N60,K40:N40,K20:N20,K1:N1	K1	=ROUNDUP(K2/12,0)
L2:N2,L121:N121,L101:N101,L81:N81,L61:N61,L41:N41,L21:N21	L2	=K2+1
J3	J3	=MINIFS(J$22:J$1005,I$22:I$1005,"date")
K3,K122,K102,K82,K62,K42,K22	K3	=EOMONTH(J3,0)
L3:N3,L122:N122,L102:N102,L82:N82,L62:N62,L42:N42,L22:N22	L3	=EOMONTH(K3,1)
K4:K7	K4	=SUMPRODUCT(($I23:$I123=$I4)*($K$22:$BG$122=K$3),$K23:$BG123)
L4:N7	L4	=SUMPRODUCT(($I23:$I103=$I4)*($K$22:$BG$102=L$3),$K23:$BG103)
K8:N8,K127:N127,K107:N107,K87:N87,K67:N67,K47:N47,K27:N27	K8	=SUM(K4:K7)
L10:N10,L129:N129,L109:N109,L89:N89,L69:N69,L49:N49,L29:N29	L10	=IF(-SUM($K8:L8)<0,0,-SUM($K8:L8))
L11:N11,L130:N130,L110:N110,L90:N90,L70:N70,L50:N50,L30:N30	L11	=L5/-SUMIF($K4:$BG4,"<0")
L12:N12,L131:N131,L111:N111,L91:N91,L71:N71,L51:N51,L31:N31	L12	=IF(L10=0,"Infinity",L5/L10)
K13	K13	=XIRR(K8:CQ8,K3:CQ3)
K14	K14	=SUMIF(K8:CQ8,">0")/-SUMIF(K8:CQ8,"<0")
K23,K123,K103,K83,K63,K43	K23	=-D29
L28:N28,L128:N128,L108:N108,L88:N88,L68:N68,L48:N48	L28	=L27/-$K27
K32,K132,K112,K92,K72,K52	K32	=XIRR(K27:BV27,K22:BV22)
K33,K133,K113,K93,K73,K53	K33	=SUMIF(K27:BV27,">0")/-SUMIF(K27:BV27,"<0")

 

[jasonb75]

The problem is the XIRR formula in K112. That is returning a #NUM! error for reasons that I can not see in the mini sheet.

You will need to fix that error then the sumproduct formula will work just fine.

There is another error in K132 that doesn't look as if it will affect the current sumproduct formula but it is likely to cause the same problem next time you copy the formula down the sheet.

 

[ExcelUser18]

You're right. I saw that error but thought because it was not part of the search category in Column I, that it would not have mattered. That works now. Sorry about that.

 

[jasonb75]

I saw that error but thought because it was not part of the search category in Column I, that it would not have mattered.

In the majority of cases an error that falls within a formula range will be carried forward regardless of whether or not that cell is matched with other criteria to form part of the result. There are some exceptions but it is considered best practice not to leave unresolved errors in your sheet.