Hi, I've read so many threads and can't seem to find examples with similar structure to mine. Need help, please.
I'm trying to sum data across multiple worksheets into a summary where it must match text from the row & the year from the columns and sum the amounts across all worksheets. Showing very simplified version below...
- The worksheets will be numbered 1 through 16 and I have a list named "sheetlist"
- There is a year referenced in row 1 across all worksheets with data, but there may be multiple of same year where the summary will need to consolidate.
- The text in column A will appear once but row may not be inconsistent.
I've tried the sumproduct(sumifs(indirect but can't seem to get it to work.
I have been able to get sumproduct to work across a single worksheet, but not all 16.
Summary sheet:
<tbody>
</tbody>
Sample worksheet 1
<tbody>
</tbody>
Sample worksheet 2
<tbody>
</tbody>
I'm trying to sum data across multiple worksheets into a summary where it must match text from the row & the year from the columns and sum the amounts across all worksheets. Showing very simplified version below...
- The worksheets will be numbered 1 through 16 and I have a list named "sheetlist"
- There is a year referenced in row 1 across all worksheets with data, but there may be multiple of same year where the summary will need to consolidate.
- The text in column A will appear once but row may not be inconsistent.
I've tried the sumproduct(sumifs(indirect but can't seem to get it to work.
I have been able to get sumproduct to work across a single worksheet, but not all 16.
Summary sheet:
A | B | C | D | E | |
1 | 2017 | 2018 | 2019 | 2020 | |
2 | REVENUE | ||||
3 | COS | ||||
4 | OH |
<tbody>
</tbody>
Sample worksheet 1
A | B | C | D | E | |
1 | 2017 | 2017 | 2017 | 2018 | |
2 | REVENUE | ||||
3 | |||||
4 | COS | ||||
5 | OH |
<tbody>
</tbody>
Sample worksheet 2
A | B | C | D | E | |
1 | 2017 | 2018 | 2018 | 2018 | |
2 | |||||
3 | REVENUE | ||||
4 | COS | ||||
5 | OH |
<tbody>
</tbody>