Search code examples
google-sheetsgoogle-sheets-formulaspreadsheetmultiplicationworksheet-function

Multiplying column values with ability to add/delete rows

I have an invoice, where there are two columns: Rate (Column H) and Quantity (Column I). And there is Amount (Column J).

I need a formula so that it would automatically multiply Rate and Quantity and show it in Amount. The number of rows would be changing (added or deleted), but the formula should still work.

enter image description here

Solution

  • The following ArrayFormula() spans from row 5 ("Books Name") to row 14 ("Total Price"), so adding rows or deleting them should not be a problem:

    =ArrayFormula(if((C5:C14="Books Name")+(C5:C14="Total Price")=1,"",H5:H14*I5:I14))

    enter image description here

    However, since the formula will have to reside in the column J, the following formula is even more useful:

    =ArrayFormula(if(C5:C14="Books Name","Amount",if(C5:C14="Total Price","",H5:H14*I5:I14)))