Conditional Sum

[jlclyde]

I am looking to add a conditional sum to a workbook. The trick is
that the information that i want to sum is another workbook. Is this
possible or do i need to write some code in VBA?

Thanks,
Jay

 

[Peo Sjoblom]

There is no difference if the other workbook is open, if it is closed then
you can use
either a SUM array formula or SUMPRODUCT (better) SUMIF however does not
work if the other workbook is closed nor does DSUM


--


Regards,


Peo Sjoblom

 

[jlclyde]

There is no difference if the other workbook is open, if it is closed then
you can use
either a SUM array formula or SUMPRODUCT (better) SUMIF however does not
work if the other workbook is closed nor does DSUM

--

Regards,

Peo Sjoblom







- Show quoted text -

I shoudl have been more descriptive of what I am looking for. I want
to set up a conditional sum from one workbook to antoher. There are
multiple conditions that I need it to meet. Here is an example
This is in work book 1
Column A is Names
Column B is output
Column C is dates

This is in workbook 2
(nothing yet) I am trying to get if Column A in workbook 1 = X and
Column C in workbook 1 = Y then Sum Column B in workbook 1.

Is this possible?
Jay

 

[Peo Sjoblom]

=SUMPRODUCT(--('[Work book 1.xls]Sheet1'!$A$1:$A$10000="X"),--('[Work book
1.xls]Sheet1'!$C$1:$C$10000=1),'[Work book 1.xls]Sheet1'!$B$1:$B$10000)


note that you cannot use A:A unless you are using Excel 2007, you need to
specify the range in previous versions

Make sure the workbook is opened when you apply the formula than Excel will
take care of paths and names etc when/if you close the other workbook


--


Regards,


Peo Sjoblom

 

[jlclyde]

=SUMPRODUCT(--('[Work book 1.xls]Sheet1'!$A$1:$A$10000="X"),--('[Work book
1.xls]Sheet1'!$C$1:$C$10000=1),'[Work book 1.xls]Sheet1'!$B$1:$B$10000)

note that you cannot use A:A unless you are using Excel 2007, you need to
specify the range in previous versions

Make sure the workbook is opened when you apply the formula than Excel will
take care of paths and names etc when/if you close the other workbook

--

Regards,

Peo Sjoblom

I shoudl have been more descriptive of what I am looking for. I want
to set up a conditional sum from one workbook to antoher. There are
multiple conditions that I need it to meet. Here is an example
This is in work book 1
Column A is Names
Column B is output
Column C is dates

This is in workbook 2
(nothing yet) I am trying to get if Column A in workbook 1 = X and
Column C in workbook 1 = Y then Sum Column B in workbook 1.

Is this possible?
Jay- Hide quoted text -

- Show quoted text -

Again, I am sure that I am not coming across clearly. I need to sum
row c in workbook 1 based on if the information in A and B are equal
to X and Y. These will be totaled on the next workbook.

Thanks,
Jay

 

[jlclyde]

=SUMPRODUCT(--('[Work book 1.xls]Sheet1'!$A$1:$A$10000="X"),--('[Work book
1.xls]Sheet1'!$C$1:$C$10000=1),'[Work book 1.xls]Sheet1'!$B$1:$B$10000)

note that you cannot use A:A unless you are using Excel 2007, you need to
specify the range in previous versions

Make sure the workbook is opened when you apply the formula than Excel will
take care of paths and names etc when/if you close the other workbook


Peo Sjoblom

- Show quoted text -

Again, I am sure that I am not coming across clearly. I need to sum
row c in workbook 1 based on if the information in A and B are equal
to X and Y. These will be totaled on the next workbook.

Thanks,
Jay- Hide quoted text -

- Show quoted text -

Here is the actual information
In workbook 2 I have a list of dates in A. At the top of the work
sheet is the Operators name and employee #. In column B I want excel
to lookup the date, the employee name and number and return the sum of
all rows that are equal to these from workbook 1. So if a row matches
date, employee name and number it will sum quantity column.

Thanks,
Jay

 

[Peo Sjoblom]

That's not what you said, you said

you wanted to sum B based on A and C


=SUMPRODUCT(--('[Work book 1.xls]Sheet1'!$A$1:$A$10000="X"),--('[Work book
1.xls]Sheet1'!$B$1:$B$10000="Y"),'[Work book 1.xls]Sheet1'!$C$1:$C$10000)



--


Regards,


Peo Sjoblom

 

[jlclyde]

That's not what you said, you said

you wanted to sum B based on A and C

=SUMPRODUCT(--('[Work book 1.xls]Sheet1'!$A$1:$A$10000="X"),--('[Work book
1.xls]Sheet1'!$B$1:$B$10000="Y"),'[Work book 1.xls]Sheet1'!$C$1:$C$10000)

--

Regards,

Peo Sjoblom

Again, I am sure that I am not coming across clearly. I need to sum
row c in workbook 1 based on if the information in A and B are equal
to X and Y. These will be totaled on the next workbook.

Thanks,
Jay- Hide quoted text -

- Show quoted text -

I just want to say that this works great. I have never thought of
using the Sumproduct this way. I am sorry that it took me so long to
get it.

Jay