How to specify almost full-column arguments like A3:A
 

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?

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

Though I doubt you have 60000 records.

--
Regards
Frank Kabel
Frankfurt, Germany

"TimRegan" schrieb im Newsbeitrag
om...
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?

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?

TimRegan wrote:
Hi All,
. . .PS Bonus kudos if you can also tell me why MONTH returns 1 if passed
an empty cell as an argument?

If the empty cell is Cell A3, then you can see that =A3 returns 0, and 0
is the date serial number for January 1, 1900, or January 2, 1904,
depending on what date system you are using.

Alan Beban