Create a formula based on the number of columns in Google Spreadsheets

Question

I have two rows of data

Row B = price of product
Row C = quantity of producst

I need to get the summary price:

(B1xC1)+(B2xC2)+(B3xC3)+..+(Bn+Cn)

I don't know n (quantity of columns) because n is changing from time to time (from 50-1000). And I don't want to specify all n in formula.

Is it possible to write a summation formula for any n?

Answer 1

Use sumproduct().

You could either change the formula every time (only involves changing two cells) or just go with:

=sumproduct(B1:B1000,C1:C1000)

Alternatively you could write yourself a short function that:

1. Checks the two input ranges are the same size
2. Calculates the size of the ranges (say 'X')
3. Runs a loop 'X' times multiplying the two range values together and maintaining a total

However I would just use sumproduct().

Answer 2

If you do not want to make the formula depending on the number of rows, you can simply use the formula =SUMPRODUCT(B:B;C:C). If you have a more elaborated need, please let me know.