Using INDIRECT Function with SUMPRODUCT

Hi,

This has been driving me mad all morning!

I am trying to multiply two ranges together (Sumproduct function), by using the indirect function. I can't seem to get it to work - when stepping through the formula, "Ref" is appearing.

I would be very grateful if someone could show me the error of my ways!

I have attached a spreadsheet to help!


I have a second question... I have often seen two dashes used in a Sumproduct construction - could someone please explain to me what this does - in all the replies I have looked through, there's no reference to this!

e.g.

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Thanks in advance for reading!

John

HI make some changes in data input as follows.
Ref
Col1 Start E6
Col1 End E12
Col2 Start F6
Col2 End F12

and then write this formula
=SUMPRODUCT(INDIRECT(C6&":"&C7),INDIRECT(C8&":"&C9))

For your second question ..that is not dash, that is - (minus) symbol. Minus converts true, false in to 1,0. and it helps in calculation.

Regards,
Suhas

Shuas,

Many thanks for both replies!
(I'd quickly made up the example so the wrong references were a blunder rather than a misunderstanding, but the use of an indirect function for each range is what I was doing wrong!)
Thank you also for clarification of the minus'. Another piece of the puzzle in place!

I can now enjoy my weekend!

Thank you!

John

Solved!

(How do I close this off?)