Conditional Sum
 

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

Conditional Sum
 

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" wrote in message
...
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

Conditional Sum
 

On Nov 27, 10:34 am, "Peo Sjoblom" wrote:
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" wrote in message

...



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- Hide quoted text -

- 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

Conditional Sum
 

=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" wrote in message
...
On Nov 27, 10:34 am, "Peo Sjoblom" wrote:
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" wrote in message

...



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- Hide quoted text -

- 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

Conditional Sum
 

On Nov 27, 11:02 am, "Peo Sjoblom" wrote:
=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" wrote in message

...



On Nov 27, 10:34 am, "Peo Sjoblom" wrote:
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" wrote in message

...

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- Hide quoted text -

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

Conditional Sum
 

On Nov 27, 12:03 pm, jlclyde wrote:
On Nov 27, 11:02 am, "Peo Sjoblom" wrote:





=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" wrote in message

...

On Nov 27, 10:34 am, "Peo Sjoblom" wrote:
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" wrote in message

...

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- Hide quoted text -

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

Conditional Sum
 

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





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" wrote in message

...



On Nov 27, 10:34 am, "Peo Sjoblom" wrote:
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" wrote in message

...

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- Hide quoted text -

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

Conditional Sum
 

On Nov 27, 12:20 pm, "Peo Sjoblom" wrote:
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





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" wrote in message

...

On Nov 27, 10:34 am, "Peo Sjoblom" wrote:
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" wrote in message

...

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- Hide quoted text -

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