Array Formula

[RHagger]

I am having trouble trying to solve a formula requirement.

I want my formula to give me the sum of a column if two criterea's are met. (i.e if column A = "CAS" and column B = "AUTO" then give me the total of column K).

However it seems when using an array formula I can only select the rows that have data populated rather than select the entire column.

For example:-

{=-sum(([ZDL00073.xls]Earn V3 Inw YTD Base'!A$:A$=A4)*( [ZDL00073.xls]Earn V3 Inw YTD Base'!$C:C$=B4)-( [ZDL00073.xls]Earn V3 Inw YTD Base'!K:K) )}

Will give me an output of #VALUE. Whereas

{=-sum(([ZDL00073.xls]Earn V3 Inw YTD Base'!A$1:A$64=A4)*( [ZDL00073.xls]Earn V3 Inw YTD Base'!$C1:C64$=B4)-( [ZDL00073.xls]Earn V3 Inw YTD Base'!K1:K64) )}

gives me the required output.

I want to select the entire column in my formula as my data source will change in size month by month and I do not want to have to edit the muiltple formula's I will be using each time but just edit the links to the new months file.

I do not want to use a SUMIF statement as this also gives a #VALUE result unless I open the file that holds the data range the formula is looking up which is not convenient as there will be multiple files.

I hope this makes sense and any idea's or solutions is greatly appeciated.

Thanks

 

[NBVC]

You can use a range that is higher than you think you will ever need... but not too high and use Sumproduct:

e.g.

=SUMPRODUCT(--('[ZDL00073.xls]Earn V3 Inw YTD Base'!A$1:A$1000=A4),--('[ZDL00073.xls]Earn V3 Inw YTD Base'!$C$1:$C$1000=B4),('[ZDL00073.xls]Earn V3 Inw YTD Base'!$K$1:$K$1000))

or you can perform the SUMIF on that workbook, and then just point directly with something like:

='[ZDL00073.xls]Earn V3 Inw YTD Base'!X1

where X1 contains the SUMIF formula on that workbook.

 

[RHagger]

Perfect!! Your SUMPRODUCT formula is doing exactly what i want it to.

Thank you for your help it is much appreciated.