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:
<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>
</tbody>
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:
Region | Account | Jan-18 | Feb-18 | Mar-18 | Apr-18 | May-18 |
North | Total Sales | 500.00 | 450.00 | 200.00 | 650.00 | 550.00 |
South | Total Sales | 425.00 | 382.50 | 170.00 | 552.50 | 467.50 |
East | Total Sales | 275.00 | 247.50 | 110.00 | 357.50 | 302.50 |
West | Total Sales | 525.00 | 472.50 | 210.00 | 682.50 | 577.50 |
North | Total COS | 325.00 | 292.50 | 130.00 | 422.50 | 357.50 |
South | Total COS | 276.25 | 248.63 | 110.50 | 359.13 | 303.88 |
East | Total COS | 178.75 | 160.88 | 71.50 | 232.38 | 196.63 |
West | Total COS | 341.25 | 307.13 | 136.50 | 443.63 | 375.38 |
North | Total Credit Sales | 250.00 | 225.00 | 100.00 | 325.00 | 275.00 |
South | Total Credit Sales | 212.50 | 191.25 | 85.00 | 276.25 | 233.75 |
East | Total Credit Sales | 137.50 | 123.75 | 55.00 | 178.75 | 151.25 |
West | Total Credit Sales | 262.50 | 236.25 | 105.00 | 341.25 | 288.75 |
Extract | Total sales and COS for North for the months April and May | |||||
Start Date | End Date | |||||
Period | 1/04/2018 | 1/05/2018 | ||||
Region | North | |||||
Account | Total Sales | 1,200.00 | ||||
Total COS | 780.00 |