SUMPRODUCT for multiple sheets

[dkade57]

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<>""))


Please advise. Thanks in advance!

 

[Travis]

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.

 

[Brian from Maui]

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

 

[dkade57]

bump

 

[just_jon]

bump

Did you not notice Brian's question?

 

[Domenic]

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!