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!

SUMPRODUCT issues
 

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!

SUMPRODUCT issues
 

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" a écrit dans le
message de news: ...
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 issues
 

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" a écrit dans le
message de news: ...
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 issues
 

=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" wrote in message
...
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" a écrit dans le
message de news: ...
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 issues
 

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

SUMPRODUCT issues
 

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

SUMPRODUCT issues
 

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

SUMPRODUCT issues
 

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

SUMPRODUCT issues
 

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