Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
3D array SUMIF
Excel 2007 PivotTable
Consolidate sheets, filter date, sum. No brainer. Not. http://www.mediafire.com/file/z2ytwt2t2zq/10_20_09.xlsx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula SUMIF with 2D sum_range array | Excel Worksheet Functions | |||
Sumif array? | Excel Worksheet Functions | |||
and < for Array Sumif ({}) | Excel Worksheet Functions | |||
use sumif with array | Excel Worksheet Functions | |||
sumif and array formulas | Excel Worksheet Functions |