SUMPRODUCT issues

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ashlynn Grace - 26 Jun 2006 17:42 GMT

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!

Reply to this Message

Jim Thomlinson - 26 Jun 2006 17:50 GMT

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

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

Signature

HTH...

Jim Thomlinson

> 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
[quoted text clipped - 18 lines]
> ---------------------------------------------
> Thanks!

Reply to this Message

Ardus Petus - 26 Jun 2006 17:50 GMT

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

> Hi. I am having issues with learning the idea behind the SUMPRODUCT
> formula
[quoted text clipped - 24 lines]
> ---------------------------------------------
> Thanks!

Reply to this Message

Ashlynn Grace - 26 Jun 2006 18:00 GMT

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?

Signature

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

> The operation you describe is doable with simple a SUMIF:
> =SUMIF([WBA.xls]Sheet1!A:A,"John Doe",[WBA.xls]Sheet1!K:K)
[quoted text clipped - 31 lines]
> > ---------------------------------------------
> > Thanks!

Reply to this Message

Bob Phillips - 26 Jun 2006 18:23 GMT

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

> 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
[quoted text clipped - 40 lines]
> > > ---------------------------------------------
> > > Thanks!

Reply to this Message

Franz Verga - 26 Jun 2006 18:25 GMT

> 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
[quoted text clipped - 4 lines]
>> 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.

Signature

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Reply to this Message

Ashlynn Grace - 26 Jun 2006 18:55 GMT

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!

> > 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
[quoted text clipped - 21 lines]
> where in A3 you can type "John Doe" (whitout quote) or use a Data Validation
> to change the value.

Reply to this Message

Franz Verga - 26 Jun 2006 19:04 GMT

> Ok... I am about to be frustrated... Here is the formula:
>
[quoted text clipped - 5 lines]
> 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

Signature

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Reply to this Message

Ashlynn Grace - 26 Jun 2006 21:13 GMT

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!

> > Ok... I am about to be frustrated... Here is the formula:
> >
[quoted text clipped - 22 lines]
>
> "Curtis B Carter" (without quotes) in B2

Reply to this Message

Franz Verga - 26 Jun 2006 21:54 GMT

> 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
[quoted text clipped - 7 lines]
> 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

Signature

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Reply to this Message