Calculate grand total from multiple rows of quantity and price/unit

[Sixwell]

Hello everyone,

I would like to calculate and present the grand total, starting from a fixed range of two columns (each 10 rows), where one has quantities, and the other has the price/unit. Since I'm not allowed to change the layout of the sheet (it's a fixed template layout...), I can not add a column for the subtotals. Also, not all rows are filled necessarily.

I had some success with this as a worksheet function: =IF(ISERROR(H30*I30);0;H30*I30)+..., but I would really like to have it as a part of a big VBA function, since colleages might alter/delete the worksheet formula.

I looked and messed around with For loops, but I can't make it work.

Can anyone help me with this?

 

[Andrew Poulsom]

Welcome to MrExcel.

Example:

=SUMPRODUCT(A1:A10,B1:B10)

 

[Sixwell]

Thanks Andrew Poulsom, I included this in my sub as a .WorksheetFunction!