0

Here is the general structure of my spreadsheet:

Start End   Total X1 Y1 X2 Y2 X3 Y3 ... Xn Yn
09:00 10:00 1560  20 15 10 30 12 80

Total = X1 * Y1 + X2 * Y2 + X3 * Y3 ... Xn + Yn

The SUMPRODUCT function does this but it requires multiple rows of data. I need the products to match up with their corresponding Start and End time records and this proved to be extremely difficult when they are spread over many rows.

Is there a way I can use SUMPRODUCT with an unlimited number of columns instead of rows?

1

In File in cell C2 there is following formula:

=ARRAYFORMULA(
  SUMPRODUCT(
   FILTER($D2:$AA2,ISODD(SEQUENCE(1,COUNT($D2:$AA2),1,1))=TRUE),
   FILTER($D2:$AA2,ISODD(SEQUENCE(1,COUNT($D2:$AA2),1,1))=FALSE)))

Where $D1:$AA2 is supposed to be range of your data for Xn Yn so you can insert columns and range will expand.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.