Sumproduct using a max

0
votes

I have two sheets with rows of data that are organized by each row representing a date in chronological order. I need the sumproduct of those rows based on the most recent date, and that cell has to change to the most recent date once a new day is upon us.

so: Sheet 1:
day 1 x y z
day 2 x1 y1 z1
day 3 x2 y2 z2

Sheet 2:
day 1 a b c
day 2 a1 b1 c1
day 3 a2 b2 c2

Basically I need the sumproduct function to do (x2)(a2)+(y2)(b2)+(z2)*(c2) and when a new day is added with x3......c3, it needs to caluclate the sumproduct for the new row without having manual parameter changes.

I tried using a max and an index function to figure out how many rows down the most recent day is, but not sure how to get the sumproduct to use that info to calculate the row to the right of the most recent day.

1
google-sheetsmaxmatchsumproduct
Welcome to Stackoveflow - please, remember to sensible tab your post. "sumproduct" isn't a useful tag here, but you're asking about "sheet": sheets in which application? Google Sheets? Excel? Calc? That's what tags are for useful =) – Mike 'Pomax' Kamermans
dropbox.com/s/enamydmaizp49sx/… – Jefferson P. Woodruff III
The spreadsheet is simple and the formula in there works. What I need the cell with the formula to do is to move the arrays automatically down a row when a new date (Sept 5 in this case) is entered with corresponding data – Jefferson P. Woodruff III

1 Answers

0
votes

Assuming a date is on the same row number on both the sheets, try:

=sum(arrayformula((A2:A4=max($A$2:$A$4))*(B2:D4)*(B7:D9)))

Have kept both "sheets" on the same sheet for easy of visual reference:

enter image description here