Aaarrggghhh....I seldom ask for help, but this one has me stumped.
I have a SUMPRODUCT formula that doesn't like me.
Works:
Does not work:
Can anybody help? The COLUMN calc correctly calculates the number, 7 since this formula is in column B, but the rest of the formula then fails with a #VALUE! error because the OFFSET only returns one result instead of an array of 56 results. If I calculate (with F9) the COLUMN function area, it returns {7} rather than just 7. So, does that mean the array nature of SUMPRODUCT breaks the COLUMN function? Hmmm.....
I have a SUMPRODUCT formula that doesn't like me.
Works:
=SUMPRODUCT(--(LEFT('Sheet1'!$B$8:$B$63,4)="60-1"),OFFSET('Sheet1'!$B$8:$B$63,0,7))
Does not work:
=SUMPRODUCT(--(LEFT('Sheet1'!$B$8:$B$63,4)="60-1"),OFFSET('Sheet1'!$B$8:$B$63,0,COLUMN()*4-1))
Can anybody help? The COLUMN calc correctly calculates the number, 7 since this formula is in column B, but the rest of the formula then fails with a #VALUE! error because the OFFSET only returns one result instead of an array of 56 results. If I calculate (with F9) the COLUMN function area, it returns {7} rather than just 7. So, does that mean the array nature of SUMPRODUCT breaks the COLUMN function? Hmmm.....