Sumproduct & arrayformula

0
votes

I have two sheets, both have rows of data where one row represents a specific date in chronological order.

The sumproduct only needs to be made on the latest date (date in below example is in column A).

I set this up with:

=sum(ARRAYFORMULA((A3:A1000=max(A3:A1000))

So assuming that I need to sumproduct the two sheets on this specific row, I can't seem to figure out how to append the sumproduct formula into the arrayformula base.

The sumproduct is pretty basic. Assuming that row 12 is the latest date, I need Sheet 1 Cell B12 to multiply by Sheet 2 Cell B12, then sheet 1 cell C12 to multiply by Sheet 2 Cell C12 and so on.

1
if-statementgoogle-sheetsgoogle-sheets-formulaarray-formulassumproduct
Could you please provide a sheet with some examples and dummy data? – alberto vielma

1 Answers

1
votes

sumproduct of whole last row of sheet1 with the same row from sheet2:

=ARRAYFORMULA(SUMPRODUCT(
 INDIRECT("Sheet1!"&ADDRESS(MAX(IF(Sheet1!A:A="",,ROW(Sheet1!A:A))), 2)&
 ":"&MAX(IF(Sheet1!A:A="",,ROW(Sheet1!A:A)))), 
 INDIRECT("Sheet2!"&ADDRESS(MAX(IF(Sheet1!A:A="",,ROW(Sheet1!A:A))), 2)&
 ":"&MAX(IF(Sheet1!A:A="",,ROW(Sheet1!A:A))))))

0