3D array SUMIF
 

I know SUMIF doesn't work for 3D.

I have multiple worksheets say named 1 to 25

I wish to add column D if column A matches the correct
date(Reconciliation!$A10).

=SUMIF('sheet1:Sheet25'!$A$4:$A$500,Reconciliation !$A10,'Sheet1:Sheet25'!$D$4:$D$500)

I am not too clever and don't have too much time to teach myself the more
complex things excel can do. Is there is a simple way around this problem
other than listing all 25 sheets.

I have multiple uses of this type of formula if I could just find an answer
to SUMIFing 3D arrays.

When answering please assume you are talking to a novice!

3D array SUMIF
 

Try the below....Make sure you have all the sheets from 1 to 25...The
sheetnames shoould ** not ** have a space in between like 'Sheet 25'

=SUMPRODUCT(SUMIF(INDIRECT("'"&"sheet"&ROW(INDIREC T("1:25"))&"'!A:A"),
Reconciliation!$A10,INDIRECT("'"&"sheet"&ROW(INDIR ECT("1:25"))&"'!D:D")))

If this post helps click Yes
---------------
Jacob Skaria


"BimboUK" wrote:

I know SUMIF doesn't work for 3D.

I have multiple worksheets say named 1 to 25

I wish to add column D if column A matches the correct
date(Reconciliation!$A10).

=SUMIF('sheet1:Sheet25'!$A$4:$A$500,Reconciliation !$A10,'Sheet1:Sheet25'!$D$4:$D$500)

I am not too clever and don't have too much time to teach myself the more
complex things excel can do. Is there is a simple way around this problem
other than listing all 25 sheets.

I have multiple uses of this type of formula if I could just find an answer
to SUMIFing 3D arrays.

When answering please assume you are talking to a novice!

3D array SUMIF
 

Excel 2007 PivotTable
Consolidate sheets, filter date, sum.
No brainer. Not.
http://www.mediafire.com/file/z2ytwt2t2zq/10_20_09.xlsx