SUMIF question
 

I have two workbooks: BUDGET and PAYCHECKS.

In the BUDGET workbook are 12 budgets (ea month). Beside each monthly
budget is a statistical section. I will reference Jan & Feb budget info for
this question. Though the format for A3 & A57 (in BUDGET) is JAN-07 & FEB-07
respectfully, the actual value is 1/1/07 & 2/1/07. I would like a generic
formula that could be copied to all 12 statistical sections of each budget
that would sum the gross (Paycheck workbook - B17:AA17) if the MONTH value of
B10:AA10 (Paycheck Workbook) equalled the MONTH value of each budget, ie A3,
A57.

I originally tried this formula in the appropriate cell in [BUDGET]:

=SUMIF([Paycheck]2007!MONTH(B10:AA10),"=MONTH(A3)",[Paycheck]2007!B17:AA17)

I figured you could not have a range with MONTH, ie MONTH(B10:AA10) but it
was the only start I had to try and figure it out. Needless to say, I am
stuck. I wanted this type of formula in an effort to minimize the change
within the formula as it is copied to each statistical section of the budget
workbook.

I greatly appreciate any help on this!!!

Les

SUMIF question
 

Try
=SUMPRODUCT(--('[Paycheck.xls]2007'!$B$1:$F$1=MONTH(A3)),'[Paycheck.xls]2007'!$B$2:$F$2)
fix the cell references to suit your need
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"WLMPilot" wrote in message
...
I have two workbooks: BUDGET and PAYCHECKS.

In the BUDGET workbook are 12 budgets (ea month). Beside each monthly
budget is a statistical section. I will reference Jan & Feb budget info
for
this question. Though the format for A3 & A57 (in BUDGET) is JAN-07 &
FEB-07
respectfully, the actual value is 1/1/07 & 2/1/07. I would like a generic
formula that could be copied to all 12 statistical sections of each budget
that would sum the gross (Paycheck workbook - B17:AA17) if the MONTH value
of
B10:AA10 (Paycheck Workbook) equalled the MONTH value of each budget, ie
A3,
A57.

I originally tried this formula in the appropriate cell in [BUDGET]:

=SUMIF([Paycheck]2007!MONTH(B10:AA10),"=MONTH(A3)",[Paycheck]2007!B17:AA17)

I figured you could not have a range with MONTH, ie MONTH(B10:AA10) but it
was the only start I had to try and figure it out. Needless to say, I am
stuck. I wanted this type of formula in an effort to minimize the change
within the formula as it is copied to each statistical section of the
budget
workbook.

I greatly appreciate any help on this!!!

Les

SUMIF question
 

Thanks for your help. For some reason I am getting a #REF error. Both
workbooks are in the same directory on a memory stick, which is in Removable
Drive E:. Does that matter?

Les



"Bernard Liengme" wrote:

Try
=SUMPRODUCT(--('[Paycheck.xls]2007'!$B$1:$F$1=MONTH(A3)),'[Paycheck.xls]2007'!$B$2:$F$2)
fix the cell references to suit your need
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"WLMPilot" wrote in message
...
I have two workbooks: BUDGET and PAYCHECKS.

In the BUDGET workbook are 12 budgets (ea month). Beside each monthly
budget is a statistical section. I will reference Jan & Feb budget info
for
this question. Though the format for A3 & A57 (in BUDGET) is JAN-07 &
FEB-07
respectfully, the actual value is 1/1/07 & 2/1/07. I would like a generic
formula that could be copied to all 12 statistical sections of each budget
that would sum the gross (Paycheck workbook - B17:AA17) if the MONTH value
of
B10:AA10 (Paycheck Workbook) equalled the MONTH value of each budget, ie
A3,
A57.

I originally tried this formula in the appropriate cell in [BUDGET]:

=SUMIF([Paycheck]2007!MONTH(B10:AA10),"=MONTH(A3)",[Paycheck]2007!B17:AA17)

I figured you could not have a range with MONTH, ie MONTH(B10:AA10) but it
was the only start I had to try and figure it out. Needless to say, I am
stuck. I wanted this type of formula in an effort to minimize the change
within the formula as it is copied to each statistical section of the
budget
workbook.

I greatly appreciate any help on this!!!

Les

SUMIF question
 

SUMPRODUCT need both files open

USB drive should work; but try hard drive

Also, try making your own formulas by "pointing"
type =SUMPRODUCT(--(
now active the Paycheck file and select the cells using the mouse
This will ensued the file name and sheet name are correct

--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"WLMPilot" wrote in message
...
Thanks for your help. For some reason I am getting a #REF error. Both
workbooks are in the same directory on a memory stick, which is in
Removable
Drive E:. Does that matter?

Les



"Bernard Liengme" wrote:

Try
=SUMPRODUCT(--('[Paycheck.xls]2007'!$B$1:$F$1=MONTH(A3)),'[Paycheck.xls]2007'!$B$2:$F$2)
fix the cell references to suit your need
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"WLMPilot" wrote in message
...
I have two workbooks: BUDGET and PAYCHECKS.

In the BUDGET workbook are 12 budgets (ea month). Beside each monthly
budget is a statistical section. I will reference Jan & Feb budget
info
for
this question. Though the format for A3 & A57 (in BUDGET) is JAN-07 &
FEB-07
respectfully, the actual value is 1/1/07 & 2/1/07. I would like a
generic
formula that could be copied to all 12 statistical sections of each
budget
that would sum the gross (Paycheck workbook - B17:AA17) if the MONTH
value
of
B10:AA10 (Paycheck Workbook) equalled the MONTH value of each budget,
ie
A3,
A57.

I originally tried this formula in the appropriate cell in [BUDGET]:

=SUMIF([Paycheck]2007!MONTH(B10:AA10),"=MONTH(A3)",[Paycheck]2007!B17:AA17)

I figured you could not have a range with MONTH, ie MONTH(B10:AA10) but
it
was the only start I had to try and figure it out. Needless to say, I
am
stuck. I wanted this type of formula in an effort to minimize the
change
within the formula as it is copied to each statistical section of the
budget
workbook.

I greatly appreciate any help on this!!!

Les

SUMIF question
 

I apologize, but I just noticed something I may not have been clear on. In
the PAYCHECK workbook, each column (B10:AA10) has a date (mm/dd/yy) that
indicates the date of the paycheck. With this in mind does there need to be
a MONTH formula for the first part of the formula (right side of the "="
sign)?

=SUMPRODUCT(--('[Paycheck.xls]2007'!$B$1:$F$1=MONTH(A3)),'[Paycheck.xls]2007'!$B$2:$F$2)

I have both files on the harddrive at home. I use the thumb drive to allow
me to work on the files at work and then I move to the hard drive.

Thanks again,
Les

"Bernard Liengme" wrote:

SUMPRODUCT need both files open

USB drive should work; but try hard drive

Also, try making your own formulas by "pointing"
type =SUMPRODUCT(--(
now active the Paycheck file and select the cells using the mouse
This will ensued the file name and sheet name are correct

--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"WLMPilot" wrote in message
...
Thanks for your help. For some reason I am getting a #REF error. Both
workbooks are in the same directory on a memory stick, which is in
Removable
Drive E:. Does that matter?

Les



"Bernard Liengme" wrote:

Try
=SUMPRODUCT(--('[Paycheck.xls]2007'!$B$1:$F$1=MONTH(A3)),'[Paycheck.xls]2007'!$B$2:$F$2)
fix the cell references to suit your need
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"WLMPilot" wrote in message
...
I have two workbooks: BUDGET and PAYCHECKS.

In the BUDGET workbook are 12 budgets (ea month). Beside each monthly
budget is a statistical section. I will reference Jan & Feb budget
info
for
this question. Though the format for A3 & A57 (in BUDGET) is JAN-07 &
FEB-07
respectfully, the actual value is 1/1/07 & 2/1/07. I would like a
generic
formula that could be copied to all 12 statistical sections of each
budget
that would sum the gross (Paycheck workbook - B17:AA17) if the MONTH
value
of
B10:AA10 (Paycheck Workbook) equalled the MONTH value of each budget,
ie
A3,
A57.

I originally tried this formula in the appropriate cell in [BUDGET]:

=SUMIF([Paycheck]2007!MONTH(B10:AA10),"=MONTH(A3)",[Paycheck]2007!B17:AA17)

I figured you could not have a range with MONTH, ie MONTH(B10:AA10) but
it
was the only start I had to try and figure it out. Needless to say, I
am
stuck. I wanted this type of formula in an effort to minimize the
change
within the formula as it is copied to each statistical section of the
budget
workbook.

I greatly appreciate any help on this!!!

Les

SUMIF question
 

"Bernard Liengme" wrote...
SUMPRODUCT need both files open

No, SUMIF requires files be open. SUMPRODUCT doesn't, but you do have to
ensure the external references are valid.

"Bernard Liengme" wrote:
Try
=SUMPRODUCT(--('[Paycheck.xls]2007'!$B$1:$F$1=MONTH(A3)),
'[Paycheck.xls]2007'!$B$2:$F$2)
....
"WLMPilot" wrote in message
....
=SUMIF([Paycheck]2007!MONTH(B10:AA10),"=MONTH(A3)",
[Paycheck]2007!B17:AA17)
....

The OP admitted the sample formula was invalid. The questions are what are
the actual workbook names, what are the actual worksheet names, what are the
range references.

SUMIF question
 

I found out what was causing the #REF error and corrected it. The issue now
has to do with pulling the MONTH value from the date range within the
"Paychecks" workbook.

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

The first part of the above formula, Paychecks.xls]2007'!$B$10:$AA$10, looks
at the row that contains a date (mm/dd/yy) for each paycheck received. How
can I pull the MONTH value from a range (B10:AA10) to see if it equals
MONTH(A3)?



"Bernard Liengme" wrote:

SUMPRODUCT need both files open

USB drive should work; but try hard drive

Also, try making your own formulas by "pointing"
type =SUMPRODUCT(--(
now active the Paycheck file and select the cells using the mouse
This will ensued the file name and sheet name are correct

--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"WLMPilot" wrote in message
...
Thanks for your help. For some reason I am getting a #REF error. Both
workbooks are in the same directory on a memory stick, which is in
Removable
Drive E:. Does that matter?

Les



"Bernard Liengme" wrote:

Try
=SUMPRODUCT(--('[Paycheck.xls]2007'!$B$1:$F$1=MONTH(A3)),'[Paycheck.xls]2007'!$B$2:$F$2)
fix the cell references to suit your need
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"WLMPilot" wrote in message
...
I have two workbooks: BUDGET and PAYCHECKS.

In the BUDGET workbook are 12 budgets (ea month). Beside each monthly
budget is a statistical section. I will reference Jan & Feb budget
info
for
this question. Though the format for A3 & A57 (in BUDGET) is JAN-07 &
FEB-07
respectfully, the actual value is 1/1/07 & 2/1/07. I would like a
generic
formula that could be copied to all 12 statistical sections of each
budget
that would sum the gross (Paycheck workbook - B17:AA17) if the MONTH
value
of
B10:AA10 (Paycheck Workbook) equalled the MONTH value of each budget,
ie
A3,
A57.

I originally tried this formula in the appropriate cell in [BUDGET]:

=SUMIF([Paycheck]2007!MONTH(B10:AA10),"=MONTH(A3)",[Paycheck]2007!B17:AA17)

I figured you could not have a range with MONTH, ie MONTH(B10:AA10) but
it
was the only start I had to try and figure it out. Needless to say, I
am
stuck. I wanted this type of formula in an effort to minimize the
change
within the formula as it is copied to each statistical section of the
budget
workbook.

I greatly appreciate any help on this!!!

Les