Selected Answer
Please try this formula in cell C2 of your worksheet Suspense in workbook Suspense.xlsx.
=SUMPRODUCT(--(OFFSET(INDIRECT(ADDRESS(2,3,1,1,$M$1)),0,0,10000)=$A2),--(OFFSET(INDIRECT(ADDRESS(2,4,1,1,$M$1)),0,0,10000)=TEXT(C$1,"@")),OFFSET(INDIRECT(ADDRESS(2,15,1,1,$M$1)),0,0,10000))
You can copy it to D2 and F2. Change the formula in E2 as follows. Then copy C2:F2 down as far as you need.
=SUM($C2:$D2)
The recurring function ADDRESS(2,3,1,1,$M$1) specifies C2 in the other workbook. 2 is the row, 3 is the column. Accordingly, ADDRESS(2,15,1,1,$M$1) specifies O2. M1 holds the workbook & sheet name (you can move it to any other location on the same worksheet). I have the following formula in M1.
="[" & M2 &" " & M3 & ".xlsx]" & M4
M2 = Athene SuspenseDB Aging v3 (the workbook's name)
M3 = '11262018 (the date). The cell format is General. If you format it is Text the leading apostrophe isn't required.
M4 = Aging Detail (the worksheet name)
You can change all these details as required. The formula in M1 will produce a proper address from the parts. The purpose of this arrangement, apart from shortening the formula in C2, is to enable you to easily change the date.
The function OFFSET(INDIRECT(ADDRESS(2,3,1,1,$M$1)),0,0,10000) specifies a range with 10,000 rows (single column) starting from C2. Accordingly, OFFSET(INDIRECT(ADDRESS(2,15,1,1,$M$1)),0,0,10000) specifies a single column range of 10,000 rows starting from O2. When you change the range size make sure to change it for all three ranges specified in the formula.
Finally, Suspense!C1:D1 and F1 have a General format whereas the account numbers in the other sheet (column D) are formatted as Text. If you format C1:F1 also as Text the formula could make the comparison without converting the format.
