SUMPRODUCT issues

Hi. I am having issues with learning the idea behind the SUMPRODUCT formula
instead of the SUMIF, as I do not want to have both workbooks open. I have 2
workbooks, lets name them Workbook A and Workbook B, where Workbook A is my
information and Workbook B is more like a report pulling info from WB A. I
want the SUMPRODUCT to search through WB A in column A for a certain name,
and then search column K for any amount of money entered (by a formula from
other cells) for that person. The only problem is, it may have multiple
entries for each person that need to be added together to get this final
number... to be placed in column C of WB B.
So, for example, if WB A has "John Doe" as the name in column A and 4
entries for his name, there are 4 amounts in column K associated with his
name to be added together and placed in cell C4 of WB B.

How can I get this to operate smoothly? The amounts will vary each month as
there are different numbers of entries and amounts within the entries each
month.

I know this is a lot to chew, but any pointers to push me in the right
direction would be wonderful!!

---------------------------------------------
Thanks!

The operation you describe is doable with simple a SUMIF:
=SUMIF([WBA.xls]Sheet1!A:A,"John Doe",[WBA.xls]Sheet1!K:K)

Cheers
--
AP

"Ashlynn Grace" <[email protected]> a écrit dans le
message de news: [email protected]...
> Hi. I am having issues with learning the idea behind the SUMPRODUCT
> formula
> instead of the SUMIF, as I do not want to have both workbooks open. I
> have 2
> workbooks, lets name them Workbook A and Workbook B, where Workbook A is
> my
> information and Workbook B is more like a report pulling info from WB A.
> I
> want the SUMPRODUCT to search through WB A in column A for a certain name,
> and then search column K for any amount of money entered (by a formula
> from
> other cells) for that person. The only problem is, it may have multiple
> entries for each person that need to be added together to get this final
> number... to be placed in column C of WB B.
> So, for example, if WB A has "John Doe" as the name in column A and 4
> entries for his name, there are 4 amounts in column K associated with his
> name to be added together and placed in cell C4 of WB B.
>
> How can I get this to operate smoothly? The amounts will vary each month
> as
> there are different numbers of entries and amounts within the entries each
> month.
>
> I know this is a lot to chew, but any pointers to push me in the right
> direction would be wonderful!!
>
> ---------------------------------------------
> Thanks!
>
>

Here is the sumproduct function from A to Z...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"Ashlynn Grace" wrote:

> Hi. I am having issues with learning the idea behind the SUMPRODUCT formula
> instead of the SUMIF, as I do not want to have both workbooks open. I have 2
> workbooks, lets name them Workbook A and Workbook B, where Workbook A is my
> information and Workbook B is more like a report pulling info from WB A. I
> want the SUMPRODUCT to search through WB A in column A for a certain name,
> and then search column K for any amount of money entered (by a formula from
> other cells) for that person. The only problem is, it may have multiple
> entries for each person that need to be added together to get this final
> number... to be placed in column C of WB B.
> So, for example, if WB A has "John Doe" as the name in column A and 4
> entries for his name, there are 4 amounts in column K associated with his
> name to be added together and placed in cell C4 of WB B.
>
> How can I get this to operate smoothly? The amounts will vary each month as
> there are different numbers of entries and amounts within the entries each
> month.
>
> I know this is a lot to chew, but any pointers to push me in the right
> direction would be wonderful!!
>
> ---------------------------------------------
> Thanks!
>
>

I really wish that I could use the SUMIF .... but doesn't that require that
both workbooks be open? I want this to work whether or not both are open. I
was told that SUMPRODUCT works to do this over SUMIF... I may be wrong. Can
I just translate that SUMIF statement over to the SUMPRODUCT, or do I need to
change anything?
--
---------------------------------------------
Thanks!


"Ardus Petus" wrote:

> The operation you describe is doable with simple a SUMIF:
> =SUMIF([WBA.xls]Sheet1!A:A,"John Doe",[WBA.xls]Sheet1!K:K)
>
> Cheers
> --
> AP
>
> "Ashlynn Grace" <[email protected]> a écrit dans le
> message de news: [email protected]...
> > Hi. I am having issues with learning the idea behind the SUMPRODUCT
> > formula
> > instead of the SUMIF, as I do not want to have both workbooks open. I
> > have 2
> > workbooks, lets name them Workbook A and Workbook B, where Workbook A is
> > my
> > information and Workbook B is more like a report pulling info from WB A.
> > I
> > want the SUMPRODUCT to search through WB A in column A for a certain name,
> > and then search column K for any amount of money entered (by a formula
> > from
> > other cells) for that person. The only problem is, it may have multiple
> > entries for each person that need to be added together to get this final
> > number... to be placed in column C of WB B.
> > So, for example, if WB A has "John Doe" as the name in column A and 4
> > entries for his name, there are 4 amounts in column K associated with his
> > name to be added together and placed in cell C4 of WB B.
> >
> > How can I get this to operate smoothly? The amounts will vary each month
> > as
> > there are different numbers of entries and amounts within the entries each
> > month.
> >
> > I know this is a lot to chew, but any pointers to push me in the right
> > direction would be wonderful!!
> >
> > ---------------------------------------------
> > Thanks!
> >
> >
>
>
>

=SUMPRODUCT(--([WBA.xls]Sheet1!A1:A1000="John
Doe"),[WBA.xls]Sheet1!K1:K1000)

Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Ashlynn Grace" <[email protected]> wrote in message
news:[email protected]...
> I really wish that I could use the SUMIF .... but doesn't that require
that
> both workbooks be open? I want this to work whether or not both are open.
I
> was told that SUMPRODUCT works to do this over SUMIF... I may be wrong.
Can
> I just translate that SUMIF statement over to the SUMPRODUCT, or do I need
to
> change anything?
> --
> ---------------------------------------------
> Thanks!
>
>
> "Ardus Petus" wrote:
>
> > The operation you describe is doable with simple a SUMIF:
> > =SUMIF([WBA.xls]Sheet1!A:A,"John Doe",[WBA.xls]Sheet1!K:K)
> >
> > Cheers
> > --
> > AP
> >
> > "Ashlynn Grace" <[email protected]> a écrit dans le
> > message de news: [email protected]...
> > > Hi. I am having issues with learning the idea behind the SUMPRODUCT
> > > formula
> > > instead of the SUMIF, as I do not want to have both workbooks open. I
> > > have 2
> > > workbooks, lets name them Workbook A and Workbook B, where Workbook A
is
> > > my
> > > information and Workbook B is more like a report pulling info from WB
A.
> > > I
> > > want the SUMPRODUCT to search through WB A in column A for a certain
name,
> > > and then search column K for any amount of money entered (by a formula
> > > from
> > > other cells) for that person. The only problem is, it may have
multiple
> > > entries for each person that need to be added together to get this
final
> > > number... to be placed in column C of WB B.
> > > So, for example, if WB A has "John Doe" as the name in column A and 4
> > > entries for his name, there are 4 amounts in column K associated with
his
> > > name to be added together and placed in cell C4 of WB B.
> > >
> > > How can I get this to operate smoothly? The amounts will vary each
month
> > > as
> > > there are different numbers of entries and amounts within the entries
each
> > > month.
> > >
> > > I know this is a lot to chew, but any pointers to push me in the right
> > > direction would be wonderful!!
> > >
> > > ---------------------------------------------
> > > Thanks!
> > >
> > >
> >
> >
> >

Ashlynn Grace wrote:
> I really wish that I could use the SUMIF .... but doesn't that
> require that both workbooks be open? I want this to work whether or
> not both are open. I was told that SUMPRODUCT works to do this over
> SUMIF... I may be wrong. Can I just translate that SUMIF statement
> over to the SUMPRODUCT, or do I need to change anything?
>
>> The operation you describe is doable with simple a SUMIF:
>> =SUMIF([WBA.xls]Sheet1!A:A,"John Doe",[WBA.xls]Sheet1!K:K)
>>

Hi Ashlynn,

SUMIF function requires both WB open.
To translate from SUMIF to SUMPRODUCT you have to remember that in
SUMPRODUCT you cannot use whole columns, so the above formula could be
translated in this way:

=SUMPRODUCT(([WBA.xls]Sheet1!A2:A1000="John
Doe")*([WBA.xls]Sheet1!K2:K1000))

or if you want the possibility to change the condition, you can use:

=SUMPRODUCT(([WBA.xls]Sheet1!A2:A1000=A3)*([WBA.xls]Sheet1!K2:K1000))

where in A3 you can type "John Doe" (whitout quote) or use a Data Validation
to change the value.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Ok... I am about to be frustrated... Here is the formula:

=SUMPRODUCT(('[Production Manager Commission Payroll
Report.xls]Sheet1'!A2:A65536=Curtis B Carter)*('[Production Manager
Commission Payroll Report.xls]Sheet1'!K2:K65536))

Why am I getting a #NAME error in the cells? This is exactly what my
workbook is named... No Clue as to what I can do to make this work like it
should.


---------------------------------------------
Thanks!


"Franz Verga" wrote:

> Ashlynn Grace wrote:
> > I really wish that I could use the SUMIF .... but doesn't that
> > require that both workbooks be open? I want this to work whether or
> > not both are open. I was told that SUMPRODUCT works to do this over
> > SUMIF... I may be wrong. Can I just translate that SUMIF statement
> > over to the SUMPRODUCT, or do I need to change anything?
> >
> >> The operation you describe is doable with simple a SUMIF:
> >> =SUMIF([WBA.xls]Sheet1!A:A,"John Doe",[WBA.xls]Sheet1!K:K)
> >>
>
> Hi Ashlynn,
>
> SUMIF function requires both WB open.
> To translate from SUMIF to SUMPRODUCT you have to remember that in
> SUMPRODUCT you cannot use whole columns, so the above formula could be
> translated in this way:
>
> =SUMPRODUCT(([WBA.xls]Sheet1!A2:A1000="John
> Doe")*([WBA.xls]Sheet1!K2:K1000))
>
> or if you want the possibility to change the condition, you can use:
>
> =SUMPRODUCT(([WBA.xls]Sheet1!A2:A1000=A3)*([WBA.xls]Sheet1!K2:K1000))
>
> where in A3 you can type "John Doe" (whitout quote) or use a Data Validation
> to change the value.
>
> --
> Hope I helped you.
>
> Thanks in advance for your feedback.
>
> Ciao
>
> Franz Verga from Italy
>
>
>

Ashlynn Grace wrote:
> Ok... I am about to be frustrated... Here is the formula:
>
> =SUMPRODUCT(('[Production Manager Commission Payroll
> Report.xls]Sheet1'!A2:A65536=Curtis B Carter)*('[Production Manager
> Commission Payroll Report.xls]Sheet1'!K2:K65536))
>
> Why am I getting a #NAME error in the cells? This is exactly what my
> workbook is named... No Clue as to what I can do to make this work
> like it should.

If you put the value of condition insiide the formula, you must type quotes
around it if it is a text value, so you can use:

=SUMPRODUCT(('[Production Manager Commission Payroll
Report.xls]Sheet1'!A2:A65536="Curtis B Carter")*('[Production Manager
Commission Payroll Report.xls]Sheet1'!K2:K65536))

or

=SUMPRODUCT(('[Production Manager Commission Payroll
Report.xls]Sheet1'!A2:A65536=B2)*('[Production Manager
Commission Payroll Report.xls]Sheet1'!K2:K65536))

and typing

"Curtis B Carter" (without quotes) in B2


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Anyone have an idea why this formula would be giving me a #REF! error? The
help guide is of no help at all. I am sure that I don't have any cells
pointing at one another or anything like that. I don't know if this has
anything to do with it, but the cells in column K have a formula in them that
pulls from other cells in the same workbook to bring over to this new
workbook, where the following formula is placed:

=SUMPRODUCT(('[Production Manager Commission Payroll
Report.xls]Sheet1'!A2:A65536="Curtis B Carter")*('[Production Manager
Commission Payroll Report.xls]Sheet1'!K2:K65536))

---------------------------------------------
Please HELP!! Thanks!


"Franz Verga" wrote:

> Ashlynn Grace wrote:
> > Ok... I am about to be frustrated... Here is the formula:
> >
> > =SUMPRODUCT(('[Production Manager Commission Payroll
> > Report.xls]Sheet1'!A2:A65536=Curtis B Carter)*('[Production Manager
> > Commission Payroll Report.xls]Sheet1'!K2:K65536))
> >
> > Why am I getting a #NAME error in the cells? This is exactly what my
> > workbook is named... No Clue as to what I can do to make this work
> > like it should.
>
> If you put the value of condition insiide the formula, you must type quotes
> around it if it is a text value, so you can use:
>
> =SUMPRODUCT(('[Production Manager Commission Payroll
> Report.xls]Sheet1'!A2:A65536="Curtis B Carter")*('[Production Manager
> Commission Payroll Report.xls]Sheet1'!K2:K65536))
>
> or
>
> =SUMPRODUCT(('[Production Manager Commission Payroll
> Report.xls]Sheet1'!A2:A65536=B2)*('[Production Manager
> Commission Payroll Report.xls]Sheet1'!K2:K65536))
>
> and typing
>
> "Curtis B Carter" (without quotes) in B2
>
>
> --
> Hope I helped you.
>
> Thanks in advance for your feedback.
>
> Ciao
>
> Franz Verga from Italy
>
>
>

Ashlynn Grace wrote:
> Anyone have an idea why this formula would be giving me a #REF!
> error? The help guide is of no help at all. I am sure that I don't
> have any cells pointing at one another or anything like that. I
> don't know if this has anything to do with it, but the cells in
> column K have a formula in them that pulls from other cells in the
> same workbook to bring over to this new workbook, where the following
> formula is placed:
>
> =SUMPRODUCT(('[Production Manager Commission Payroll
> Report.xls]Sheet1'!A2:A65536="Curtis B Carter")*('[Production Manager
> Commission Payroll Report.xls]Sheet1'!K2:K65536))

It's seem very strange...

I think this should work...

Try this way

=SUMPRODUCT(--('[Production Manager Commission Payroll
Report.xls]Sheet1'!A2:A65536="Curtis B Carter"),('[Production Manager
Commission Payroll Report.xls]Sheet1'!K2:K65536))

If this should not work, you can try to post an example workbook at
www.savefile.com

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy