Indirect Sumproduct

The formula:

=sumproduct((A1:A2)*indirect(B1))

works, but when I try to use a range of indirectly defined values:

=sumproduct((A1:A2)*indirect(B1:B2))

I get a Value error. Have I got the syntax wrong? Is there another way to do this?

Thanks

Can you post a .zip sample of your spreadsheet showing what you are trying to do?

Here it is. I need to prevent the raw salaries from appearing
in the sheet everyone is viewing.

Thanks for your help.

By the way, I've tried using a comma instead of the *, and I've tried saving the formula as an array formula; neither worked.

Sorry - I didn't edit the second reference formula.

Try this formula instead and let me know if it works.

=SUMPRODUCT((A1:A2)*(INDIRECT(B1):INDIRECT(B2)))

That's a winner! Thanks a lot!

Glad I could help.

Thanks for your help, but the formula does not continue to work when the indirect values are named on a different sheet. It seems to add up the indirect values and multiply the total against the values in the other range, instead of multiplying each pair.

At other times, when the direct range includes a value that is not associated with a named range, the formula sometimes gives a Value or N/A error, but sometimes multiplies the next available named range in the indirect range against the unmatched direct value, and instead of an error, just shows an incorrect value. Pretty scary!