SUMPRODUCT on all sheets with sheet multiplier

Question

I have multiple sheets that have the same layout but have different products and quantities on each sheet.

Then I have a different sheet that I'm using to total all the other sheets quantities per product I'm using this formula =SUMPRODUCT(SUMIF(INDIRECT("'"&D$8:D$10&"'!B3:B6"),B3,INDIRECT("'"&D$8:D$10&"'!A3:A6"))) Which works fine.

How can I sum all the product quantities from the same sheet more than once, For example if I add the number of times I want to multiply the sheet In column E next to each sheet name and I put a qty of 2 in E8 it would then double the quantity for that sheet well still summing all the other sheets.

Answer 1

You can use Named Ranges for this. Give the Cell "E8" a name - "Takeoff1_Multiplier" and in the Sheet Takeoff1, go to cell "A3" and in the formula bar enter the formula =1*Takeoff1_Multiplier.

Now, when you change the value in "E8", the value in "A3" will be multiplied.

If you don't want to use a formula in the Column A in the Takeoff1 sheet, use the formula in column c as =A3*Takeoff1_Multiplier and drag this formula for all the rows.

Resources:

Named Range: https://support.office.com/en-us/article/define-and-use-names-in-formulas-4d0f13ac-53b7-422e-afd2-abd7ff379c64

Answer 2

=SUMPRODUCT($E8:$10*SUMIF(INDIRECT("'"&D$8:D$10&"'!B3:B6"),B3,INDIRECT("'"&D$8:D$10&"'!A3:A6")))
or
=SUMPRODUCT($E8:$10,SUMIF(INDIRECT("'"&D$8:D$10&"'!B3:B6"),B3,INDIRECT("'"&D$8:D$10&"'!A3:A6")))

Both methods should work. Basically you are weighting the results of each sumif sheet result by the corresponding number n your table before taking the final sum/grand total.