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

ExcelUser18

New Member
Joined
May 3, 2017
Messages
34
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.

Year0111111111111222222222
Month0123456789101112131415161718192021
Date3/16/20204/30/20205/31/20206/30/20207/31/20208/31/20209/30/202010/31/202011/30/202012/31/20201/31/20212/28/20213/31/20214/30/20215/31/20216/30/20217/31/20218/31/20219/30/202110/31/202111/30/202112/31/2021
CF-30,000.000.000.000.000.000.000.000.000.000.000.000.00232.50213.550.000.00106.050.000.00143.980.000.00
Refi/Sale0.000.000.000.000.000.000.000.0010,010.000.000.000.000.000.0010,226.000.000.000.000.000.000.000.00
Total-30,000.000.000.000.000.000.000.000.0010,010.000.000.000.00232.50213.5510,226.000.00106.050.000.00143.980.000.00
Year01111111111112
Month012345678910111213
Date3/16/20204/30/20205/31/20206/30/20207/31/20208/31/20209/30/202010/31/202011/30/202012/31/20201/31/20212/28/20213/31/20214/30/2021
CF-30,000.000.000.000.000.000.000.000.000.000.000.000.00232.500.00
Refi/Sale0.000.000.000.000.000.000.000.0010,010.000.000.000.000.000.00
Total-30,000.000.000.000.000.000.000.000.0010,010.000.000.000.00232.500.00
Year01111111111112
Month012345678910111213
Date12/24/20201/31/20212/28/20213/31/20214/30/20215/31/20216/30/20217/31/20218/31/20219/30/202110/31/202111/30/202112/31/20211/31/2022
CF-30,000.000.000.000.000.000.000.000.000.000.000.000.000.000.00
Refi/Sale0.000.000.000.000.0010,226.000.000.000.000.000.000.000.000.00
Total-30,000.000.000.000.000.0010,226.000.000.000.000.000.000.000.000.00
Year01111111111112
Month012345678910111213
Date11/1/202012/31/20201/31/20212/28/20213/31/20214/30/20215/31/20216/30/20217/31/20218/31/20219/30/202110/31/202111/30/202112/31/2021
CF-10,000.000.000.000.000.00213.550.000.00106.050.000.00143.980.000.00
Refi/Sale0.000.000.000.000.000.000.000.000.000.000.000.000.000.00
Total-10,000.000.000.000.000.00213.550.000.00106.050.000.00143.980.000.00
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.
Excel Formula:
=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.
 
Upvote 0
Sorry about that, see the mini sheet below:

MrExcel - 10.25.1.xlsx
BCDEFGHIJKLMNOPQRSTUVWXY
6Year0111111111111222222222
7Month0123456789101112131415161718192021
8Date3/16/20204/30/20205/31/20206/30/20207/31/20208/31/20209/30/202010/31/202011/30/202012/31/20201/31/20212/28/20213/31/20214/30/20215/31/20216/30/20217/31/20218/31/20219/30/202110/31/202111/30/202112/31/2021
9CF-30,000.000.000.000.000.000.000.000.000.000.000.000.00232.50213.550.000.00106.050.000.00143.980.000.00
10Refi/Sale0.000.000.000.000.000.000.000.0010,010.000.000.000.000.000.0010,226.000.000.000.000.000.000.000.00
11Total-30,000.000.000.000.000.000.000.000.0010,010.000.000.000.00232.50213.5510,226.000.00106.050.000.00143.980.000.00
12
13
14
15
16Year01111111111112
17Month012345678910111213
18Date3/16/20204/30/20205/31/20206/30/20207/31/20208/31/20209/30/202010/31/202011/30/202012/31/20201/31/20212/28/20213/31/20214/30/2021
19CF-30,000.000.000.000.000.000.000.000.000.000.000.000.00232.500.00
20Refi/Sale0.000.000.000.000.000.000.000.0010,010.000.000.000.000.000.00
21Total-30,000.000.000.000.000.000.000.000.0010,010.000.000.000.00232.500.00
22
23
24
25Year01111111111112
26Month012345678910111213
27Date12/24/20201/31/20212/28/20213/31/20214/30/20215/31/20216/30/20217/31/20218/31/20219/30/202110/31/202111/30/202112/31/20211/31/2022
28CF-30,000.000.000.000.000.000.000.000.000.000.000.000.000.000.00
29Refi/Sale0.000.000.000.000.0010,226.000.000.000.000.000.000.000.000.00
30Total-30,000.000.000.000.000.0010,226.000.000.000.000.000.000.000.000.00
31
32
33
34Year01111111111112
35Month012345678910111213
36Date11/1/202012/31/20201/31/20212/28/20213/31/20214/30/20215/31/20216/30/20217/31/20218/31/20219/30/202110/31/202111/30/202112/31/2021
37CF-10,000.000.000.000.000.00213.550.000.00106.050.000.00143.980.000.00
38Refi/Sale0.000.000.000.000.000.000.000.000.000.000.000.000.000.00
39Total-10,000.000.000.000.000.00213.550.000.00106.050.000.00143.980.000.00
40
Sheet3
Cell Formulas
RangeFormula
E6:Y6E6=ROUNDUP(E7/12,0)
E7:Y7E7=D7+1
D8D8=MIN(D18,D27,D36)
E8:Y8E8=EOMONTH(D8,1)
D9:Y9D9=SUMIF($D18:$Q18,D$8,$D19:$Q19)+SUMIF($D27:$Q27,D$8,$D28:$Q28)+SUMIF($D36:$Q36,D$8,$D37:$Q37)
D10:Y10D10=SUMIF($D18:$Q18,D$8,$D20:$Q20)+SUMIF($D27:$Q27,D$8,$D29:$Q29)+SUMIF($D36:$Q36,D$8,$D38:$Q38)
D11:Y11D11=SUM(D9:D10)
 
Upvote 0
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.
 
Upvote 0
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.
Excel Formula:
=MINIFS(D$18:D$1000,C$18:C$1000,"Date")
 
Last edited:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top