Hi, I wonder if it's possible to create an excel formula that can specify a sum range to be used in sumifs fx if there were 3 criteria used just to show what row to look at.
Basically I need to specify to search in column find all the cell rows that have a certain value and then within that result check the next column to find the row that has a certain value then in that row add the cells based on a specified column header. The last bit is where the sumifs come in, summing based on date.
I can get the same info using pivot but my data changes every month and I want to be able to just refresh the summary rather than copy/pasting values from the pivot table since i'll be doing this for at least 100 worksheets therefor 100 pivot tables.
Below is an example I hope it makes things clearer:
[TABLE="width: 421"]
<colgroup><col width="121" style="width: 91pt; mso-width-source: userset; mso-width-alt: 4425;"> <col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;"> <col width="64" style="width: 48pt;" span="4"> <tbody>[TR]
[TD="width: 121, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]Region[/TD]
[TD="width: 72, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]Account[/TD]
[TD="width: 112, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]Jan-18[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]Feb-18[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]Mar-18[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]Apr-18[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]May-18[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]North[/TD]
[TD="bgcolor: transparent"]Total Sales[/TD]
[TD="bgcolor: transparent"] 500.00 [/TD]
[TD="bgcolor: transparent"] 450.00 [/TD]
[TD="bgcolor: transparent"] 200.00 [/TD]
[TD="bgcolor: transparent"] 650.00 [/TD]
[TD="bgcolor: transparent"] 550.00 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]South[/TD]
[TD="bgcolor: transparent"]Total Sales[/TD]
[TD="bgcolor: transparent"] 425.00 [/TD]
[TD="bgcolor: transparent"] 382.50 [/TD]
[TD="bgcolor: transparent"] 170.00 [/TD]
[TD="bgcolor: transparent"] 552.50 [/TD]
[TD="bgcolor: transparent"] 467.50 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]East[/TD]
[TD="bgcolor: transparent"]Total Sales[/TD]
[TD="bgcolor: transparent"] 275.00 [/TD]
[TD="bgcolor: transparent"] 247.50 [/TD]
[TD="bgcolor: transparent"] 110.00 [/TD]
[TD="bgcolor: transparent"] 357.50 [/TD]
[TD="bgcolor: transparent"] 302.50 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]West[/TD]
[TD="bgcolor: transparent"]Total Sales[/TD]
[TD="bgcolor: transparent"] 525.00 [/TD]
[TD="bgcolor: transparent"] 472.50 [/TD]
[TD="bgcolor: transparent"] 210.00 [/TD]
[TD="bgcolor: transparent"] 682.50 [/TD]
[TD="bgcolor: transparent"] 577.50 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]North[/TD]
[TD="bgcolor: transparent"]Total COS[/TD]
[TD="bgcolor: transparent"] 325.00 [/TD]
[TD="bgcolor: transparent"] 292.50 [/TD]
[TD="bgcolor: transparent"] 130.00 [/TD]
[TD="bgcolor: transparent"] 422.50 [/TD]
[TD="bgcolor: transparent"] 357.50 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]South[/TD]
[TD="bgcolor: transparent"]Total COS[/TD]
[TD="bgcolor: transparent"] 276.25 [/TD]
[TD="bgcolor: transparent"] 248.63 [/TD]
[TD="bgcolor: transparent"] 110.50 [/TD]
[TD="bgcolor: transparent"] 359.13 [/TD]
[TD="bgcolor: transparent"] 303.88 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]East[/TD]
[TD="bgcolor: transparent"]Total COS[/TD]
[TD="bgcolor: transparent"] 178.75 [/TD]
[TD="bgcolor: transparent"] 160.88 [/TD]
[TD="bgcolor: transparent"] 71.50 [/TD]
[TD="bgcolor: transparent"] 232.38 [/TD]
[TD="bgcolor: transparent"] 196.63 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]West[/TD]
[TD="bgcolor: transparent"]Total COS[/TD]
[TD="bgcolor: transparent"] 341.25 [/TD]
[TD="bgcolor: transparent"] 307.13 [/TD]
[TD="bgcolor: transparent"] 136.50 [/TD]
[TD="bgcolor: transparent"] 443.63 [/TD]
[TD="bgcolor: transparent"] 375.38 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]North[/TD]
[TD="bgcolor: transparent"]Total Credit Sales[/TD]
[TD="bgcolor: transparent"] 250.00 [/TD]
[TD="bgcolor: transparent"] 225.00 [/TD]
[TD="bgcolor: transparent"] 100.00 [/TD]
[TD="bgcolor: transparent"] 325.00 [/TD]
[TD="bgcolor: transparent"] 275.00 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]South[/TD]
[TD="bgcolor: transparent"]Total Credit Sales[/TD]
[TD="bgcolor: transparent"] 212.50 [/TD]
[TD="bgcolor: transparent"] 191.25 [/TD]
[TD="bgcolor: transparent"] 85.00 [/TD]
[TD="bgcolor: transparent"] 276.25 [/TD]
[TD="bgcolor: transparent"] 233.75 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]East[/TD]
[TD="bgcolor: transparent"]Total Credit Sales[/TD]
[TD="bgcolor: transparent"] 137.50 [/TD]
[TD="bgcolor: transparent"] 123.75 [/TD]
[TD="bgcolor: transparent"] 55.00 [/TD]
[TD="bgcolor: transparent"] 178.75 [/TD]
[TD="bgcolor: transparent"] 151.25 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]West[/TD]
[TD="bgcolor: transparent"]Total Credit Sales[/TD]
[TD="bgcolor: transparent"] 262.50 [/TD]
[TD="bgcolor: transparent"] 236.25 [/TD]
[TD="bgcolor: transparent"] 105.00 [/TD]
[TD="bgcolor: transparent"] 341.25 [/TD]
[TD="bgcolor: transparent"] 288.75 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Extract[/TD]
[TD="bgcolor: transparent, colspan: 5"]Total sales and COS for North for the months April and May[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Start Date[/TD]
[TD="bgcolor: transparent"]End Date[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Period[/TD]
[TD="bgcolor: transparent, align: right"]1/04/2018[/TD]
[TD="bgcolor: transparent, align: right"]1/05/2018[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Region[/TD]
[TD="bgcolor: transparent"]North[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Account[/TD]
[TD="bgcolor: transparent"]Total Sales[/TD]
[TD="bgcolor: transparent"] 1,200.00 [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Total COS[/TD]
[TD="bgcolor: transparent"] 780.00 [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
Basically I need to specify to search in column find all the cell rows that have a certain value and then within that result check the next column to find the row that has a certain value then in that row add the cells based on a specified column header. The last bit is where the sumifs come in, summing based on date.
I can get the same info using pivot but my data changes every month and I want to be able to just refresh the summary rather than copy/pasting values from the pivot table since i'll be doing this for at least 100 worksheets therefor 100 pivot tables.
Below is an example I hope it makes things clearer:
[TABLE="width: 421"]
<colgroup><col width="121" style="width: 91pt; mso-width-source: userset; mso-width-alt: 4425;"> <col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;"> <col width="64" style="width: 48pt;" span="4"> <tbody>[TR]
[TD="width: 121, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]Region[/TD]
[TD="width: 72, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]Account[/TD]
[TD="width: 112, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]Jan-18[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]Feb-18[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]Mar-18[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]Apr-18[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]May-18[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]North[/TD]
[TD="bgcolor: transparent"]Total Sales[/TD]
[TD="bgcolor: transparent"] 500.00 [/TD]
[TD="bgcolor: transparent"] 450.00 [/TD]
[TD="bgcolor: transparent"] 200.00 [/TD]
[TD="bgcolor: transparent"] 650.00 [/TD]
[TD="bgcolor: transparent"] 550.00 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]South[/TD]
[TD="bgcolor: transparent"]Total Sales[/TD]
[TD="bgcolor: transparent"] 425.00 [/TD]
[TD="bgcolor: transparent"] 382.50 [/TD]
[TD="bgcolor: transparent"] 170.00 [/TD]
[TD="bgcolor: transparent"] 552.50 [/TD]
[TD="bgcolor: transparent"] 467.50 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]East[/TD]
[TD="bgcolor: transparent"]Total Sales[/TD]
[TD="bgcolor: transparent"] 275.00 [/TD]
[TD="bgcolor: transparent"] 247.50 [/TD]
[TD="bgcolor: transparent"] 110.00 [/TD]
[TD="bgcolor: transparent"] 357.50 [/TD]
[TD="bgcolor: transparent"] 302.50 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]West[/TD]
[TD="bgcolor: transparent"]Total Sales[/TD]
[TD="bgcolor: transparent"] 525.00 [/TD]
[TD="bgcolor: transparent"] 472.50 [/TD]
[TD="bgcolor: transparent"] 210.00 [/TD]
[TD="bgcolor: transparent"] 682.50 [/TD]
[TD="bgcolor: transparent"] 577.50 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]North[/TD]
[TD="bgcolor: transparent"]Total COS[/TD]
[TD="bgcolor: transparent"] 325.00 [/TD]
[TD="bgcolor: transparent"] 292.50 [/TD]
[TD="bgcolor: transparent"] 130.00 [/TD]
[TD="bgcolor: transparent"] 422.50 [/TD]
[TD="bgcolor: transparent"] 357.50 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]South[/TD]
[TD="bgcolor: transparent"]Total COS[/TD]
[TD="bgcolor: transparent"] 276.25 [/TD]
[TD="bgcolor: transparent"] 248.63 [/TD]
[TD="bgcolor: transparent"] 110.50 [/TD]
[TD="bgcolor: transparent"] 359.13 [/TD]
[TD="bgcolor: transparent"] 303.88 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]East[/TD]
[TD="bgcolor: transparent"]Total COS[/TD]
[TD="bgcolor: transparent"] 178.75 [/TD]
[TD="bgcolor: transparent"] 160.88 [/TD]
[TD="bgcolor: transparent"] 71.50 [/TD]
[TD="bgcolor: transparent"] 232.38 [/TD]
[TD="bgcolor: transparent"] 196.63 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]West[/TD]
[TD="bgcolor: transparent"]Total COS[/TD]
[TD="bgcolor: transparent"] 341.25 [/TD]
[TD="bgcolor: transparent"] 307.13 [/TD]
[TD="bgcolor: transparent"] 136.50 [/TD]
[TD="bgcolor: transparent"] 443.63 [/TD]
[TD="bgcolor: transparent"] 375.38 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]North[/TD]
[TD="bgcolor: transparent"]Total Credit Sales[/TD]
[TD="bgcolor: transparent"] 250.00 [/TD]
[TD="bgcolor: transparent"] 225.00 [/TD]
[TD="bgcolor: transparent"] 100.00 [/TD]
[TD="bgcolor: transparent"] 325.00 [/TD]
[TD="bgcolor: transparent"] 275.00 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]South[/TD]
[TD="bgcolor: transparent"]Total Credit Sales[/TD]
[TD="bgcolor: transparent"] 212.50 [/TD]
[TD="bgcolor: transparent"] 191.25 [/TD]
[TD="bgcolor: transparent"] 85.00 [/TD]
[TD="bgcolor: transparent"] 276.25 [/TD]
[TD="bgcolor: transparent"] 233.75 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]East[/TD]
[TD="bgcolor: transparent"]Total Credit Sales[/TD]
[TD="bgcolor: transparent"] 137.50 [/TD]
[TD="bgcolor: transparent"] 123.75 [/TD]
[TD="bgcolor: transparent"] 55.00 [/TD]
[TD="bgcolor: transparent"] 178.75 [/TD]
[TD="bgcolor: transparent"] 151.25 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]West[/TD]
[TD="bgcolor: transparent"]Total Credit Sales[/TD]
[TD="bgcolor: transparent"] 262.50 [/TD]
[TD="bgcolor: transparent"] 236.25 [/TD]
[TD="bgcolor: transparent"] 105.00 [/TD]
[TD="bgcolor: transparent"] 341.25 [/TD]
[TD="bgcolor: transparent"] 288.75 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Extract[/TD]
[TD="bgcolor: transparent, colspan: 5"]Total sales and COS for North for the months April and May[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Start Date[/TD]
[TD="bgcolor: transparent"]End Date[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Period[/TD]
[TD="bgcolor: transparent, align: right"]1/04/2018[/TD]
[TD="bgcolor: transparent, align: right"]1/05/2018[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Region[/TD]
[TD="bgcolor: transparent"]North[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Account[/TD]
[TD="bgcolor: transparent"]Total Sales[/TD]
[TD="bgcolor: transparent"] 1,200.00 [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Total COS[/TD]
[TD="bgcolor: transparent"] 780.00 [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]