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]

What is in T2?

And welcome to the forum!


If possible, please install the xl2bb addin so you can paste all of your miniworkbooks here and help make it easier for the folks in the forum to help you

 

[oldman33]

I'm not able to install on this computer unfortunately. Here are some screenshots

I'm trying to setup an output table that looks like this. My formulas are in cells E2 and E3.

Sheet2, Sheet3, Sheet4 all look like this. I'm trying to sum the numbers in rows 2 and 3 if the condition matches row 1.

Note my previous example went out to column T. This goes to column B for simplicity

This formula works to pull one row from all of the sheets.

=SUMPRODUCT(SUMIF(INDIRECT(ADDRESS(1,1,,,$A$1:$A$3)&":B1"),E$1,INDIRECT(ADDRESS(2,1,,,$A$1:$A$3)&":B2")))

This formula is what's broken. I changed the sum range to be rows 2 and 3 of the different sheets, but it only sums from row 2

=SUMPRODUCT(SUMIF(INDIRECT(ADDRESS(1,1,,,$A$1:$A$3)&":B1"),E$1,INDIRECT(ADDRESS(2,1,,,$A$1:$A$3)&":B3")))

Many thanks!

 

[awoohaw]

Can you give me an example of what the correct calculations would look like or tell me what the calculation is?
Are you summing rows 2 & 3 on the other three sheets and summarizing them on the first sheet?
Are you looking for 1 value in each row for each sheet, and the sum of that?

 

[oldman33]

Can you give me an example of what the correct calculations would look like or tell me what the calculation is?
Are you summing rows 2 & 3 on the other three sheets and summarizing them on the first sheet?
Are you looking for 1 value in each row for each sheet, and the sum of that?

In Cell E2, the correct result for the screenshots would = sum(Sheet2!A2:A3) + sum(Sheet3!A2:A3) + sum(Sheet4!A2:A3)

This is an oversimplification since the variable header row in sheets2/3/4 will change, hence the use of sumif()

Does that make sense?

 

[awoohaw]

I think so. Are the values in rows of the other two sheets all positive, or positive in one row/negative in the other. (just so I can set up my workbooks).

 

[oldman33]

I think they'll always be positive, but they're random numbers. I used:

=ROUND(RAND()*100,0)

 

[awoohaw]

Okay. I use Randarray(rows,cols,min,max,integer)... Randarray(2,20,10,75,1) will give a 20x2 matrix of random integers between 10 and 75. working on it!

 

[oldman33]

Cool, that's a new formula for me!

 

[awoohaw]

Cool, that's a new formula for me!

Lots of incredibly new formulas in 365!