I have a need to take a cell from one worksheet and multiply it with another cell on a second worksheet, and continue the same for the entire
row / range of cells, then add them together for a total sum.

SUM(Sheet1!A4*Sheet2!A2 + Sheet1!A5*Sheet2!A3 + Sheet1!A6*Sheet2!A4) - etc

The above formula works, however when I attempt to put it in a range across the entire row, using " : ", it continues to give me an error, even as an array. Ideally I am would like to do SUM(Sheet1!A4*Sheet2!A2 : Sheet1!A6*Sheet2!A4) to pick up the entire row. Sum and Sumif seem to have a problem with the calculation.