SUMPRODUCT for multiple sheets

New Member
I have read a few different posts related but I'm still not getting this to work.

I have a workbook with 31 different worksheets named "1", "2", "3"... and so on. I want to get this formula but a total from all 31 sheets not just "1".

=SUMPRODUCT(--('1'!\$D\$1:\$D\$4000=\$A3),--('1'!\$T\$1:\$T\$4000=1),--('1'!\$B\$1:\$B\$4000<>""),--('1'!\$A\$1:\$A\$4000=1),--('1'!\$U\$1:\$U\$4000<>""))

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).
I think you would have to do a big sum

=SUMPRODUCT(--('1'!\$D\$1:\$D\$4000=\$A3),--('1'!\$T\$1:\$T\$4000=1),--('1'!\$B\$1:\$B\$4000<>""),--('1'!\$A\$1:\$A\$4000=1),--('1'!\$U\$1:\$U\$4000<>"")) +
=SUMPRODUCT(--('2'!\$D\$1:\$D\$4000=\$A3),--('2'!\$T\$1:\$T\$4000=1),--('2'!\$B\$1:\$B\$4000<>""),--('2'!\$A\$1:\$A\$4000=1),--('2'!\$U\$1:\$U\$4000<>"")) ...

might be easier to put the sumproduct on each sheet, then just have the master sheet sum all those.

Is there a range to total from all 31 sheets or one cell from 31 sheets?

bump

Did you not notice Brian's question?

I believe you you'll be able to use the THREED function if you download and install the free add-in Morefunc.xll. Otherwise, if you don't particularly like Travis' second suggestion, I think you'd have to use something like the following formula...

=SUMPRODUCT(--(T(OFFSET(INDIRECT(A1:AE1&"!D1:D4000"),ROW(INDIRECT("1:4000"))-1,0,1))=\$A3),--(N(OFFSET(INDIRECT(A1:AE1&"!T1:T4000"),ROW(INDIRECT("1:4000"))-1,0,1))=1),--(N(OFFSET(INDIRECT(A1:AE1&"!B1:B4000"),ROW(INDIRECT("1:4000"))-1,0,1))<>""),--(N(OFFSET(INDIRECT(A1:AE1&"!A1:A4000"),ROW(INDIRECT("1:4000"))-1,0,1))=1),--(N(OFFSET(INDIRECT(A1:AE1&"!U1:U4000"),ROW(INDIRECT("1:4000"))-1,0,1))<>""))

...where A1:AE1 contain numbers 1 through 31. I've assumed that Column D contains text values, and that other columns contain numerical values. If this is not correct, change the letters T and N accordingly. For example, if Column D contains numerical values instead, change the T to an N. Note that I've tested this formula using only three sheets, and found it somewhat slow. I imagine it'll be pretty slow with 31 sheets.

Hope this helps!

Replies
5
Views
423
Replies
48
Views
1K
Replies
4
Views
117
Replies
11
Views
298
Replies
9
Views
137

1,203,380
Messages
6,055,098
Members
444,761
Latest member
lneuberger

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.

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

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