ExcelBanter - Conditional Sum

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Conditional Sum (https://www.excelbanter.com/excel-worksheet-functions/205311-conditional-sum.html)

Hello,

Can someone please help with this formula.

I have a worksheet called Template and another called Tenure 9.14.08 and the
tab on that spreadsheet is called "Previous Week."

On my Template spreadsheet I am trying to set up a formula that will look in
Column A for an X in the Tenure 9.14.08 spreadsheet. And if it sees and X it
will give me a sum of Column C of all of the rows that have an X.

For instance:

A B C
X Mary 12
George 2
X Bob 24
X Henry 8

I would like the formula to look at Column A and if there is an X add the
data that is in column C. So my result would be 44. Hopefully this makes
sense.

I have tried =SUMPRODUCT(('[Lux Tenure 9.14.08.xls]Previous
Week'!$A:$A="x"),'[Lux Tenure 9.14.08.xls]Previous Week'!$C:$C))

But it is oh so very, very wrong.

Thanks for your help in advance!

What version of Excel are you using? Unless you're using Excel 2007 you
*can't* use entire columns as range references. Try it like this:

=SUMPRODUCT(--('[Lux Tenure 9.14.08.xls]Previous Week'!$A1:$A10="x"),'[Lux
Tenure 9.14.08.xls]Previous Week'!$C1:$C10)

--
Biff
Microsoft Excel MVP

"Herb" wrote in message
...
Hello,

Can someone please help with this formula.

I have a worksheet called Template and another called Tenure 9.14.08 and
the
tab on that spreadsheet is called "Previous Week."

On my Template spreadsheet I am trying to set up a formula that will look
in
Column A for an X in the Tenure 9.14.08 spreadsheet. And if it sees and X
it
will give me a sum of Column C of all of the rows that have an X.

For instance:

A B C
X Mary 12
George 2
X Bob 24
X Henry 8

I would like the formula to look at Column A and if there is an X add the
data that is in column C. So my result would be 44. Hopefully this makes
sense.

I have tried =SUMPRODUCT(('[Lux Tenure 9.14.08.xls]Previous
Week'!$A:$A="x"),'[Lux Tenure 9.14.08.xls]Previous Week'!$C:$C))

But it is oh so very, very wrong.

Thanks for your help in advance!

Try this:

=Sumif(A:A,"X",C:C)

Add your path since your description is confusing.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Herb" wrote in message
...
Hello,

Can someone please help with this formula.

I have a worksheet called Template and another called Tenure 9.14.08 and
the
tab on that spreadsheet is called "Previous Week."

On my Template spreadsheet I am trying to set up a formula that will look
in
Column A for an X in the Tenure 9.14.08 spreadsheet. And if it sees and X
it
will give me a sum of Column C of all of the rows that have an X.

For instance:

A B C
X Mary 12
George 2
X Bob 24
X Henry 8

I would like the formula to look at Column A and if there is an X add the
data that is in column C. So my result would be 44. Hopefully this makes
sense.

I have tried =SUMPRODUCT(('[Lux Tenure 9.14.08.xls]Previous
Week'!$A:$A="x"),'[Lux Tenure 9.14.08.xls]Previous Week'!$C:$C))

But it is oh so very, very wrong.

Thanks for your help in advance!

Herb,

When working with a SUMPRODUCT you cannot use Columns, you have to specify
the range (i.e. A1:A10 instead of A:A). Secondly, when it does return the
answer it is going to be placed in TRUE/FALSE instead of the 1/0 which you
require. Placing a double negation will allow your data to come in as 1/0.
Therefore, the code you would wish to use is such:

=SUMPRODUCT(--($A1:$A10="x"),$C1:$C10)

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''''Yes'''' below.

"Herb" wrote:

Hello,

Can someone please help with this formula.

I have a worksheet called Template and another called Tenure 9.14.08 and the
tab on that spreadsheet is called "Previous Week."

On my Template spreadsheet I am trying to set up a formula that will look in
Column A for an X in the Tenure 9.14.08 spreadsheet. And if it sees and X it
will give me a sum of Column C of all of the rows that have an X.

For instance:

A B C
X Mary 12
George 2
X Bob 24
X Henry 8

I would like the formula to look at Column A and if there is an X add the
data that is in column C. So my result would be 44. Hopefully this makes
sense.

I have tried =SUMPRODUCT(('[Lux Tenure 9.14.08.xls]Previous
Week'!$A:$A="x"),'[Lux Tenure 9.14.08.xls]Previous Week'!$C:$C))

But it is oh so very, very wrong.

Thanks for your help in advance!

Sorry, I didn't read to the end of your post where you used the path as an
example.

Try this:

=SUMIF('[Lux Tenure 9.14.08.xls]Previous Week'!$A:$A,"X",'[Lux Tenure
9.14.08.xls]Previous Week'!$C:$C)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"RagDyer" wrote in message
...
Try this:

=Sumif(A:A,"X",C:C)

Add your path since your description is confusing.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Herb" wrote in message
...
Hello,

Can someone please help with this formula.

I have a worksheet called Template and another called Tenure 9.14.08 and
the
tab on that spreadsheet is called "Previous Week."

On my Template spreadsheet I am trying to set up a formula that will look
in
Column A for an X in the Tenure 9.14.08 spreadsheet. And if it sees and X
it
will give me a sum of Column C of all of the rows that have an X.

For instance:

A B C
X Mary 12
George 2
X Bob 24
X Henry 8

I would like the formula to look at Column A and if there is an X add the
data that is in column C. So my result would be 44. Hopefully this makes
sense.

I have tried =SUMPRODUCT(('[Lux Tenure 9.14.08.xls]Previous
Week'!$A:$A="x"),'[Lux Tenure 9.14.08.xls]Previous Week'!$C:$C))

But it is oh so very, very wrong.

Thanks for your help in advance!

Of course, the WB must be open for the Sumif() function to work.

You already have solutions if that's not convenient.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"RagDyer" wrote in message
...
Sorry, I didn't read to the end of your post where you used the path as an
example.

Try this:

=SUMIF('[Lux Tenure 9.14.08.xls]Previous Week'!$A:$A,"X",'[Lux Tenure
9.14.08.xls]Previous Week'!$C:$C)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"RagDyer" wrote in message
...
Try this:

=Sumif(A:A,"X",C:C)

Add your path since your description is confusing.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Herb" wrote in message
...
Hello,

Can someone please help with this formula.

I have a worksheet called Template and another called Tenure 9.14.08 and
the
tab on that spreadsheet is called "Previous Week."

On my Template spreadsheet I am trying to set up a formula that will
look in
Column A for an X in the Tenure 9.14.08 spreadsheet. And if it sees and
X it
will give me a sum of Column C of all of the rows that have an X.

For instance:

A B C
X Mary 12
George 2
X Bob 24
X Henry 8

I would like the formula to look at Column A and if there is an X add
the
data that is in column C. So my result would be 44. Hopefully this makes
sense.

I have tried =SUMPRODUCT(('[Lux Tenure 9.14.08.xls]Previous
Week'!$A:$A="x"),'[Lux Tenure 9.14.08.xls]Previous Week'!$C:$C))

But it is oh so very, very wrong.

Thanks for your help in advance!

Thank you both so much for your help on this! I knew it was something simple
but I just couldn't get it.

Thanks again!

"Herb" wrote:

Hello,

Can someone please help with this formula.

I have a worksheet called Template and another called Tenure 9.14.08 and the
tab on that spreadsheet is called "Previous Week."

On my Template spreadsheet I am trying to set up a formula that will look in
Column A for an X in the Tenure 9.14.08 spreadsheet. And if it sees and X it
will give me a sum of Column C of all of the rows that have an X.

For instance:

A B C
X Mary 12
George 2
X Bob 24
X Henry 8

I would like the formula to look at Column A and if there is an X add the
data that is in column C. So my result would be 44. Hopefully this makes
sense.

I have tried =SUMPRODUCT(('[Lux Tenure 9.14.08.xls]Previous
Week'!$A:$A="x"),'[Lux Tenure 9.14.08.xls]Previous Week'!$C:$C))

But it is oh so very, very wrong.

Thanks for your help in advance!