If possible put headers as text box and not in the cells. That way you can
use D:D type expressions.

"TimRegan" wrote:

Hi All,

I have some header rows in a worksheet that are preventing a formula
from working. The formula is:

=SUMPRODUCT(--(MONTH('Income 04_05'!A:A)=B7),('Income 04_05'!D:D))

but it fails with a #NUM! error because the columns 'Income 04_05'!A:A
and 'Income 04_05'!D:D contain two rows of header text. I tried to
miss these out by specifying the full-column less the first two rows
as the argument, like this:

=SUMPRODUCT(--(MONTH('Income 04_05'!A3:A)=B7),('Income 04_05'!D3:D))

But this isn't valid (i.e. I get a #NAME? error). I've settled for:

=SUMPRODUCT(--(MONTH('Income 04_05'!$A$3:$A$9000)=B7),('Income
04_05'!$D$3:$D$9000))

but this is clearly a cludge. How would I specify the full-column but
without the first two rows as an argument to a function?

Thanks,

Tim.

PS Bonus kudos if you can also tell me why MONTH returns 1 if passed
an empty cell as an argument?