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

 

[jasonb75]

Unsure if sumifs and index match match would be appropriate or if it would be a sumproduct with various arrays.

Sumproduct with that layout, the sheet is too badly designed for anything else.

Note that formula assumes that the rows will always be the same in each block of the table, Date, CF, Refi/Sale, Total. If the order changes, or if rows are added or removed it will not work.

=SUMPRODUCT(($C19:$C37=$C9)*($D$18:$Q$36=D$8),$D19:$Q37)

Second point makes no sense with an incomplete mini sheet. When creating the sheet with XL2BB, please check the 'Cell Formulas' box and click the 'Mini Sheet' button, not the 'Table Only' button.

 

[ExcelUser18]

Sorry about that, see the mini sheet below:

MrExcel - 10.25.1.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
6		Year	0	1	1	1	1	1	1	1	1	1	1	1	1	2	2	2	2	2	2	2	2	2
7		Month	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21
8		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
9		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
10		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
11		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
12
13
14
15
16		Year	0	1	1	1	1	1	1	1	1	1	1	1	1	2
17		Month	0	1	2	3	4	5	6	7	8	9	10	11	12	13
18		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
19		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
20		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
21		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
22
23
24
25		Year	0	1	1	1	1	1	1	1	1	1	1	1	1	2
26		Month	0	1	2	3	4	5	6	7	8	9	10	11	12	13
27		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
28		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
29		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
30		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
31
32
33
34		Year	0	1	1	1	1	1	1	1	1	1	1	1	1	2
35		Month	0	1	2	3	4	5	6	7	8	9	10	11	12	13
36		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
37		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
38		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
39		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
40
Sheet3

Cell Formulas
Range	Formula
E6:Y6	E6	=ROUNDUP(E7/12,0)
E7:Y7	E7	=D7+1
D8	D8	=MIN(D18,D27,D36)
E8:Y8	E8	=EOMONTH(D8,1)
D9:Y9	D9	=SUMIF($D18:$Q18,D$8,$D19:$Q19)+SUMIF($D27:$Q27,D$8,$D28:$Q28)+SUMIF($D36:$Q36,D$8,$D37:$Q37)
D10:Y10	D10	=SUMIF($D18:$Q18,D$8,$D20:$Q20)+SUMIF($D27:$Q27,D$8,$D29:$Q29)+SUMIF($D36:$Q36,D$8,$D38:$Q38)
D11:Y11	D11	=SUM(D9:D10)

 

[ExcelUser18]

Now that the mini-sheet is pasted, can you reflect the formulas again? I'd like to make sure I line up the correct cells within the sumproduct formula. Of cousre if you can add the minif, that would also be helpful.

 

[jasonb75]

I'd like to make sure I line up the correct cells within the sumproduct formula.

The sumproduct formula is fine subject to the conditions that I mentioned. I already had the sheet lined up correctly based on how you described it.

One of the main problems with using the table option is that the dates corrupt when the sheet is copied by anyone in europe.

This should do what you need for the minifs part.

=MINIFS(D$18:D$1000,C$18:C$1000,"Date")

 

[ExcelUser18]

Thanks Jasonb75. This worked great. Assuming when I want to add or delete rows, I just have to make sure the data set below matches up to the set up in order for the sum to work?

 

[jasonb75]

Assuming when I want to add or delete rows, I just have to make sure the data set below matches up to the set up in order for the sum to work?

That is correct, the way that the formula is set up it will always be looking for CF in the row below the date and Refi/Sale 2 rows below.

If you insert a row between CF and Refi/Sale in any part of the sheet, then you would need to insert a row between CF and Refi/Sale in each of the 4 blocks of data for the formula to continue working.

 

[ExcelUser18]

Hi Jasonb75. I'm having an issue inserting another set of data, with the same exact formulas/format below that isn't summing up above. I'm getting a #NUM! error. I moved the formula blocks to the exact same space/cell as the one above it and now its giving an error. Not sure how to provide/explain.

 

[bebo021999]

It seems the 1st table (with orange) is data that extracted from table 2,3,..,n below
And it seems date in column D8:Dn are unique?
If it is, using INDEX(MATCH,COLUMN running count) is better than SUMIF

 

[jasonb75]

I'm getting a #NUM! error. I moved the formula blocks to the exact same space/cell as the one above it and now its giving an error.

Can you post a mini sheet showing the error please, ideally with the data that the formula is looking at as well.