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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
15,474
Office Version
  1. 365
Platform
  1. Windows
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

ExcelUser18

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

ExcelUser18

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
15,474
Office Version
  1. 365
Platform
  1. Windows
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

ExcelUser18

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
15,474
Office Version
  1. 365
Platform
  1. Windows
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

ExcelUser18

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

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
2,052
Office Version
  1. 2016
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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
15,474
Office Version
  1. 365
Platform
  1. Windows
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,187,026
Messages
5,961,170
Members
438,521
Latest member
Ailana

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
Top