sumif or sumproduct with multiple sheets & rows

[oldman33]

I'm trying to write a sumif or sumproduct formula that pulls from multiple tabs and multiple rows. I've been successful with multiple tabs, but it falls apart when multiple rows are added

Basic Setup
On Sheet1 I have the tab names:
A1 Sheet2
A2 Sheet3
A3 Sheet4

Sheets 2, 3, 4 are all setup with years in row 1, and values that I want to sum in rows 2 & 3, columns 1 through 20.

Formula on Sheet1 for summing 1 row that works is:
=sumproduct(sumif(indirect(address(1,1,,,A1:A3)&:"T1"),2023,indirect(address(2,1,,,A1:A3)&:"T2"))

Any modifications to make expand the sum rows either returns the sum from 1 row, or an error.

Any suggestions?

 

[awoohaw]

Okay, I still need some clarification. What do you want on the main summary sheet? On each row do you want the value of the sums of the two rows of each sheet. For instance, on row4 of the detail sheets, do you want columns A thru T summed?
and the values of those sums reflected in columns C thru S on the summary sheet? And column B of the summary sheet to show the sum of each row? Then in the last row of the summary sheet to see the sum of column B?

 

[oldman33]

Okay, I still need some clarification. What do you want on the main summary sheet? On each row do you want the value of the sums of the two rows of each sheet. For instance, on row4 of the detail sheets, do you want columns A thru T summed?
and the values of those sums reflected in columns C thru S on the summary sheet? And column B of the summary sheet to show the sum of each row? Then in the last row of the summary sheet to see the sum of column B?

If you forget the multiple data sheets for a moment. The output page would look something like this:

Where cells B2:D2 would have the formula

=SUMPRODUCT((Sheet2!$A$1:$J$1=B$1)*(Sheet2!$A$2:$J$2))

And cells B3:D:3 would have the formula

=SUMPRODUCT((Sheet2!$A$1:$J$1=B$1)*(Sheet2!$A$3:$J$5))

I expanded Sheet2 data a bit to equal this table, starting in Sheet2 cell A1

2023​	2023​	2023​	2023​	2024​	2024​	2024​	2025​	2025​	2025​
1​	13​	50​	5​	46​	27​	18​	59​	55​	72​
1​	40​	26​	90​	59​	56​	60​	15​	39​	31​
8​	45​	21​	34​	62​	63​	11​	76​	77​	56​
94​	71​	97​	54​	28​	100​	64​	60​	23​	99​

The formulas provided above work great for a single 'data' sheet execution.

I'm trying to create 2 dynamic variables:

1. On my summary tab, I'd like a list of all the data sheets that are in the model. That way I can add or delete data tabs pretty easily without having to change the formula each time. That's what I was trying to accomplish with the indirect/address formulas​
2. For each sum row on my summary page (rows 2 & 3 in the first screenshot), I want to be able to change the sum rows that are pulled from the data sheets. All data sheets are setup exactly the same. For purposes of the summary table, sometimes I want to sum just 1 row (using the formula above in cell B2), and sometimes I want to be able to sum multiple rows (the issue I can't solve) (shown in cell B3 above). The rows to be summed may change a bit, so while adding a single totals row on each data sheet works, it isn't the most convenient.​

I hope that helps