SUMIF question

[Guest]

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

 

[Bernard Liengme]

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

 

[Guest]

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

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

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

 

[Bernard Liengme]

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

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

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

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

 

[Guest]

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

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

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

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

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

 

[Harlan Grove]

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.

Try
=SUMPRODUCT(--('[Paycheck.xls]2007'!$B$1:$F$1=MONTH(A3)),
'[Paycheck.xls]2007'!$B$2:$F$2) ....
=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.

 

[Guest]

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)?

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

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

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

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