MONTH() question

W

WLMPilot

I have the following formula in workbook BUDGET:

=SUMPRODUCT(--('[Paycheck.xls]2007'!$B$10:$AA$10=MONTH(A3)),'[Paycheck.xls]2007'!$B$17:$AA$17)

As you can see, it is looking in workbook PAYCHECKS for certain data. In
the PAYCHECKS workbook, range B10:AA10 contains a date (each column in row)
that a paycheck is received. The range B17:AA17 is the gross pay of each
paycheck.

MONTH(A3) is the date , ie 1/1/07 (formatted JAN-07) in BUDGET.

I am wanting to take the month value of A3 and sum the corresponding gross
pay recieved within that month. However, I do not know how to, or if you
can, use the MONTH function for a range, ie MONTH(B17:AA17).

Is there some way to do this or do I need to figure something else out?

Thanks,
Les
 
T

Toppers

try:

=SUMPRODUCT(--(MONTH('[Paycheck.xls]2007'!$B$10:$AA$10)=MONTH(A3)),'[Paycheck.xls]2007'!$B$17:$AA$17)
 
D

David Biddulph

MONTH() gives a number from 1 to 12, so it doesn't make sense to format that
as a date (where it would appear as 1st Jan 1900 to 12th Jan 1900,
inclusive), but perhaps you mean that A3, rather than MONTH(A3), is
formatted as a date?
If your range B10:AA10 contains real dates, you can't realistically test
that directly for equality with your month 1 to 12 in MONTH(A3), so it may
make sense to change your formula to:
=SUMPRODUCT(--(MONTH('[Paycheck.xls]2007'!$B$10:$AA$10)=MONTH(A3)),'[Paycheck.xls]2007'!$B$17:$AA$17).
 
W

WLMPilot

Thanks, just what I was looking for!!!

Toppers said:
try:

=SUMPRODUCT(--(MONTH('[Paycheck.xls]2007'!$B$10:$AA$10)=MONTH(A3)),'[Paycheck.xls]2007'!$B$17:$AA$17)

WLMPilot said:
I have the following formula in workbook BUDGET:

=SUMPRODUCT(--('[Paycheck.xls]2007'!$B$10:$AA$10=MONTH(A3)),'[Paycheck.xls]2007'!$B$17:$AA$17)

As you can see, it is looking in workbook PAYCHECKS for certain data. In
the PAYCHECKS workbook, range B10:AA10 contains a date (each column in row)
that a paycheck is received. The range B17:AA17 is the gross pay of each
paycheck.

MONTH(A3) is the date , ie 1/1/07 (formatted JAN-07) in BUDGET.

I am wanting to take the month value of A3 and sum the corresponding gross
pay recieved within that month. However, I do not know how to, or if you
can, use the MONTH function for a range, ie MONTH(B17:AA17).

Is there some way to do this or do I need to figure something else out?

Thanks,
Les
 
W

WLMPilot

One other little question: What are the two dashes for prior to the first
MONTH() function?

Les



Toppers said:
try:

=SUMPRODUCT(--(MONTH('[Paycheck.xls]2007'!$B$10:$AA$10)=MONTH(A3)),'[Paycheck.xls]2007'!$B$17:$AA$17)

WLMPilot said:
I have the following formula in workbook BUDGET:

=SUMPRODUCT(--('[Paycheck.xls]2007'!$B$10:$AA$10=MONTH(A3)),'[Paycheck.xls]2007'!$B$17:$AA$17)

As you can see, it is looking in workbook PAYCHECKS for certain data. In
the PAYCHECKS workbook, range B10:AA10 contains a date (each column in row)
that a paycheck is received. The range B17:AA17 is the gross pay of each
paycheck.

MONTH(A3) is the date , ie 1/1/07 (formatted JAN-07) in BUDGET.

I am wanting to take the month value of A3 and sum the corresponding gross
pay recieved within that month. However, I do not know how to, or if you
can, use the MONTH function for a range, ie MONTH(B17:AA17).

Is there some way to do this or do I need to figure something else out?

Thanks,
Les
 
D

David Biddulph

Double unary minus. Converts the logical TRUE or FALSE to number 0 or 1 so
that it can be multiplied..
--
David Biddulph

WLMPilot said:
One other little question: What are the two dashes for prior to the first
MONTH() function?

Les



Toppers said:
try:

=SUMPRODUCT(--(MONTH('[Paycheck.xls]2007'!$B$10:$AA$10)=MONTH(A3)),'[Paycheck.xls]2007'!$B$17:$AA$17)

WLMPilot said:
I have the following formula in workbook BUDGET:

=SUMPRODUCT(--('[Paycheck.xls]2007'!$B$10:$AA$10=MONTH(A3)),'[Paycheck.xls]2007'!$B$17:$AA$17)

As you can see, it is looking in workbook PAYCHECKS for certain data.
In
the PAYCHECKS workbook, range B10:AA10 contains a date (each column in
row)
that a paycheck is received. The range B17:AA17 is the gross pay of
each
paycheck.

MONTH(A3) is the date , ie 1/1/07 (formatted JAN-07) in BUDGET.

I am wanting to take the month value of A3 and sum the corresponding
gross
pay recieved within that month. However, I do not know how to, or if
you
can, use the MONTH function for a range, ie MONTH(B17:AA17).

Is there some way to do this or do I need to figure something else out?

Thanks,
Les
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top