Excel: #VALUE error when using SUMPRODUCT and INDIRECT

This is a weird one...

This gives #VALUE error:

     =SUMPRODUCT(INDIRECT("CP!"&ROW()&":"&ROW()),INDIRECT("CP!"&COLUMN()&":"&COLUMN()))

but this works:

     =SUMPRODUCT(INDIRECT("CP!"&SUM(ROW())&":"&SUM(ROW())),INDIRECT("CP!"&SUM(COLUMN())&":"&SUM(COLUMN())))

Got the answer from http://stackoverflow.com/questions/22746000/using-indirect-function-in-the-sumproduct-formula-for-true-false-cell-refere:

I'm not sure why you need INDIRECT instead of ordinary cell references but the specific problem here is that ROW function returns an "array", even when it returns a single value, e.g. it returns {"x"} rather than just "x" - and in some circumstances Excel can't process that.

Try wrapping the second ROW function in a SUM function - the value doesn't change but it gets rid of the array, i.e.

=SUMPRODUCT((INDIRECT("A2"):INDIRECT("A"&(ROW()-1))=INDIRECT("A"&SUM(ROW())))*1)

This will eliminate #VALUE! eror while leaving the essential structure of your formula unchanged.