Summarise data across multiple sheets (sum by year without helper column and sum minimum value)

fishmonger

New Member
Joined
Jul 28, 2022
Messages
12
Office Version
  1. 2016
Platform
  1. 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
EXCEL QUESTION.xlsx
D
13
Summary

EXCEL QUESTION.xlsx
ABCDEFGHIJK
12025234Total withdrawl (sum of withdrawal column B on each sheet based on year only (full date is in column A)
2202639
3
4
527Balance 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
RangeFormula
B1B1=SUM(qwe!B3:B14+asd!B3:B14+zxc!B3:B14)
B2B2=qwe!B15+asd!B15+zxc!B15
A5A5=SUM(MIN(qwe!C2:C15),MIN(asd!C2:C15),MIN(zxc!C2:C15))
 

Attachments

  • Screenshot 2025-01-25 135737.png
    Screenshot 2025-01-25 135737.png
    151.6 KB · Views: 4
Last edited:

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Forum statistics

Threads
1,225,969
Messages
6,188,111
Members
453,460
Latest member
Cjohnson3

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
Back
Top