fishmonger
New Member
- Joined
- Jul 28, 2022
- Messages
- 12
- Office Version
- 2016
- Platform
- Windows
I am trying to have summary sheet that will sum amounts across multiple sheets (20+) based on the year and also find the minimum value on each sheet and sum the minimum.
There are some sheets in between that have data I would want to exclude so I cannot use 3d reference)
I was using helper column to add a column with year only in each sheet and then combination of indirect + sumif based on the year column. But i am trying to achieve the summary without a helper column. Is there a way to do this? Sheets qwe,asd,zxc contain the data (have used the same data for the example). Thank you
There are some sheets in between that have data I would want to exclude so I cannot use 3d reference)
I was using helper column to add a column with year only in each sheet and then combination of indirect + sumif based on the year column. But i am trying to achieve the summary without a helper column. Is there a way to do this? Sheets qwe,asd,zxc contain the data (have used the same data for the example). Thank you
EXCEL QUESTION.xlsx | |||
---|---|---|---|
D | |||
13 | |||
Summary |
EXCEL QUESTION.xlsx | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | 2025 | 234 | Total withdrawl (sum of withdrawal column B on each sheet based on year only (full date is in column A) | ||||||||||
2 | 2026 | 39 | |||||||||||
3 | |||||||||||||
4 | |||||||||||||
5 | 27 | Balance at end (sum of minimum value in column C on each sheet) | |||||||||||
6 | |||||||||||||
7 | |||||||||||||
8 | |||||||||||||
9 | |||||||||||||
10 | |||||||||||||
11 | |||||||||||||
12 | |||||||||||||
13 | |||||||||||||
14 | |||||||||||||
15 | |||||||||||||
Summary |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B1 | B1 | =SUM(qwe!B3:B14+asd!B3:B14+zxc!B3:B14) |
B2 | B2 | =qwe!B15+asd!B15+zxc!B15 |
A5 | A5 | =SUM(MIN(qwe!C2:C15),MIN(asd!C2:C15),MIN(zxc!C2:C15)) |
Attachments
Last edited: