Google-sheets – Sum of products in a single row

google sheets

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?

Best Answer

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.

Related Solutions

Google-sheets – Cross referencing two sheets and pulling in values

This is what the command VLOOKUP is for.

Put in C1 =VLOOKUP(A1, Sheet2!A:C, 3, FALSE) and drag down the row. Here,

A1 is the entry to search for
Sheet2!A:C is the range in which the first column (A) is to be searched
3 is the column number from which to take the result (that is, C)
FALSE requires exact match, and does not assume the keys are sorted in the second sheet. If they are, consider omitting this (after reading the docs), but then if there is no match, you'll get a neighboring value instead of #N/A error.

You can also make the formula expand automatically as new data are added, with the help of ARRAYFORMULA:

=ARRAYFORMULA(IF(LEN(A1:A), VLOOKUP(A1:A, Sheet2!A:C, 3, FALSE), ""))

The logic is same as above, except there's a condition saying that empty cells in A column shouldn't be passed to VLOOKUP.

Google-sheets – Tally duration, client, client category in each Google sheets Timesheet

I'll outline the strategy in three steps. Some of them may be combined, but doing things step by step makes the process more manageable.

Import ranges

Start by pulling the data from assorted spreadsheets into one summary spreadsheet. For example, create a separate sheet/tab for each source, enter =importrange(..., "A:E") in A1, and something like

=arrayformula(if(A:A <> "", {"This_Client", "This_Category"}, ))

to mark the client and category for the source.

Combine data

In a summary sheet, enter

={filter(Sheet1!A:G, len(Sheet1!A:A)); filter(Sheet2!A:G, len(Sheet2!A:A)) }

to combine all the sources, excluding empty rows.

Query data

Use a query to summarize data and present selected columns.

=query(Summary!A:G, "select A, sum(B), F, G group by A, F, G")