MONTH() question

[Guest]

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

 

[Guest]

try:

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

 

[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).

 

[Guest]

Thanks, just what I was looking for!!!

try:

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

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

 

[Guest]

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

Les

try:

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

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

 

[David Biddulph]

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

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

Les

try:

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

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