Conditional Sum - dragging
 

I am looking to use the conditional sum feature for summing a range of data
based on a criteria of course, but I am looking to be able to set the
criteria to a cell value so that I can drag the conditional sum to other
cells and have it calcuate accordingly. Any ideas?

Conditional Sum - dragging
 

Suppose you have names in column A and amounts in column B, and you
want to add up the amounts for each name. Put your names (John, Fred,
Dave etc) in column D, say, and this formula in E1:

=SUMIF(A:A,D1,B:B)

You can then copy this formula down column E, and the D1 reference
will change to D2, D3 etc, thus picking up each name in turn.

Hope this helps.

Pete

On Feb 27, 3:36*pm, Garver wrote:
I am looking to use the conditional sum feature for summing a range of data
based on a criteria of course, but I am looking to be able to set the
criteria to a cell value so that I can drag the conditional sum to other
cells and have it calcuate accordingly. *Any ideas? *

Conditional Sum - dragging
 

You can use something along the lines of the following:

=SUMIF(A1:A16,$D$1&"=10")
where D1 is absolutely refernced or
=SUMIF(A1:A16,D1&"=10")
if relative

--
Kevin Backmann


"Garver" wrote:

I am looking to use the conditional sum feature for summing a range of data
based on a criteria of course, but I am looking to be able to set the
criteria to a cell value so that I can drag the conditional sum to other
cells and have it calcuate accordingly. Any ideas?

Conditional Sum - dragging
 

This is the formula that by using the conditional formula comes up with:

=SUM(IF('January Data'!$A$3:$A$294=7071,'January Data'!$C$3:$C$294,0))

I am looking to be able to change the 7071 to a cell value and then
vertically drag/copy this cell's value to a set of cells.

I believe the issue is that the actual formula is

{=SUM(IF('January Data'!$A$3:$A$294=7071,'January Data'!$C$3:$C$294,0))}

But as soon as you click on the cell to edit the { } go away and then it
returns a value of 0.00.

Any suggestions?

"Kevin B" wrote:

You can use something along the lines of the following:

=SUMIF(A1:A16,$D$1&"=10")
where D1 is absolutely refernced or
=SUMIF(A1:A16,D1&"=10")
if relative

--
Kevin Backmann


"Garver" wrote:

I am looking to use the conditional sum feature for summing a range of data
based on a criteria of course, but I am looking to be able to set the
criteria to a cell value so that I can drag the conditional sum to other
cells and have it calcuate accordingly. Any ideas?

Conditional Sum - dragging
 

Edit the formula to change the 7071 to a cell reference, but because
this is an arry formula you must then use CTRL-SHIFT-ENTER (CSE) to
commit the formula rather than the usual ENTER. The curly braces { }
can then be seen in the formula bar if you do this correctly. You can
then copy the formula down in the normal manner.

Whenever you edit an array formula, you must use CSE again.

Hope this helps.

Pete

On Feb 27, 4:14*pm, Garver wrote:
This is the formula that by using the conditional formula comes up with:

=SUM(IF('January Data'!$A$3:$A$294=7071,'January Data'!$C$3:$C$294,0))

I am looking to be able to change the 7071 to a cell value and then
vertically drag/copy this cell's value to a set of cells. *

I believe the issue is that the actual formula is

{=SUM(IF('January Data'!$A$3:$A$294=7071,'January Data'!$C$3:$C$294,0))}

But as soon as you click on the cell to edit the { } go away and then it
returns a value of 0.00. *

Any suggestions?



"Kevin B" wrote:
You can use something along the lines of the following:

=SUMIF(A1:A16,$D$1&"=10")
where D1 is absolutely refernced or
=SUMIF(A1:A16,D1&"=10")
if relative

--
Kevin Backmann

"Garver" wrote:

I am looking to use the conditional sum feature for summing a range of data
based on a criteria of course, but I am looking to be able to set the
criteria to a cell value so that I can drag the conditional sum to other
cells and have it calcuate accordingly. *Any ideas? *- Hide quoted text -

- Show quoted text -

Conditional Sum - dragging
 

Great! Worked perfect and saved a ton of time. I didn't know about the CSE
feature.

Garver

"Pete_UK" wrote:

Edit the formula to change the 7071 to a cell reference, but because
this is an arry formula you must then use CTRL-SHIFT-ENTER (CSE) to
commit the formula rather than the usual ENTER. The curly braces { }
can then be seen in the formula bar if you do this correctly. You can
then copy the formula down in the normal manner.

Whenever you edit an array formula, you must use CSE again.

Hope this helps.

Pete

On Feb 27, 4:14 pm, Garver wrote:
This is the formula that by using the conditional formula comes up with:

=SUM(IF('January Data'!$A$3:$A$294=7071,'January Data'!$C$3:$C$294,0))

I am looking to be able to change the 7071 to a cell value and then
vertically drag/copy this cell's value to a set of cells.

I believe the issue is that the actual formula is

{=SUM(IF('January Data'!$A$3:$A$294=7071,'January Data'!$C$3:$C$294,0))}

But as soon as you click on the cell to edit the { } go away and then it
returns a value of 0.00.

Any suggestions?



"Kevin B" wrote:
You can use something along the lines of the following:

=SUMIF(A1:A16,$D$1&"=10")
where D1 is absolutely refernced or
=SUMIF(A1:A16,D1&"=10")
if relative

--
Kevin Backmann

"Garver" wrote:

I am looking to use the conditional sum feature for summing a range of data
based on a criteria of course, but I am looking to be able to set the
criteria to a cell value so that I can drag the conditional sum to other
cells and have it calcuate accordingly. Any ideas? - Hide quoted text -

- Show quoted text -

Conditional Sum - dragging
 

Glad to be of help, Garver - thanks for feeding back.

Pete

On Feb 27, 4:51*pm, Garver wrote:
Great! *Worked perfect and saved a ton of time. *I didn't know about the CSE
feature.

Garver



"Pete_UK" wrote:
Edit the formula to change the 7071 to a cell reference, but because
this is an arry formula you must then use CTRL-SHIFT-ENTER (CSE) to
commit the formula rather than the usual ENTER. The curly braces { }
can then be seen in the formula bar if you do this correctly. You can
then copy the formula down in the normal manner.

Whenever you edit an array formula, you must use CSE again.

Hope this helps.

Pete

On Feb 27, 4:14 pm, Garver wrote:
This is the formula that by using the conditional formula comes up with:

=SUM(IF('January Data'!$A$3:$A$294=7071,'January Data'!$C$3:$C$294,0))

I am looking to be able to change the 7071 to a cell value and then
vertically drag/copy this cell's value to a set of cells. *

I believe the issue is that the actual formula is

{=SUM(IF('January Data'!$A$3:$A$294=7071,'January Data'!$C$3:$C$294,0))}

But as soon as you click on the cell to edit the { } go away and then it
returns a value of 0.00. *

Any suggestions?

"Kevin B" wrote:
You can use something along the lines of the following:

=SUMIF(A1:A16,$D$1&"=10")
where D1 is absolutely refernced or
=SUMIF(A1:A16,D1&"=10")
if relative

--
Kevin Backmann

"Garver" wrote:

I am looking to use the conditional sum feature for summing a range of data
based on a criteria of course, but I am looking to be able to set the
criteria to a cell value so that I can drag the conditional sum to other
cells and have it calcuate accordingly. *Any ideas? *- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Conditional Sum - dragging
 

Pete you helped me too

"Pete_UK" wrote:

Glad to be of help, Garver - thanks for feeding back.

Pete

On Feb 27, 4:51 pm, Garver wrote:
Great! Worked perfect and saved a ton of time. I didn't know about the CSE
feature.

Garver



"Pete_UK" wrote:
Edit the formula to change the 7071 to a cell reference, but because
this is an arry formula you must then use CTRL-SHIFT-ENTER (CSE) to
commit the formula rather than the usual ENTER. The curly braces { }
can then be seen in the formula bar if you do this correctly. You can
then copy the formula down in the normal manner.

Whenever you edit an array formula, you must use CSE again.

Hope this helps.

Pete

On Feb 27, 4:14 pm, Garver wrote:
This is the formula that by using the conditional formula comes up with:

=SUM(IF('January Data'!$A$3:$A$294=7071,'January Data'!$C$3:$C$294,0))

I am looking to be able to change the 7071 to a cell value and then
vertically drag/copy this cell's value to a set of cells.

I believe the issue is that the actual formula is

{=SUM(IF('January Data'!$A$3:$A$294=7071,'January Data'!$C$3:$C$294,0))}

But as soon as you click on the cell to edit the { } go away and then it
returns a value of 0.00.

Any suggestions?

"Kevin B" wrote:
You can use something along the lines of the following:

=SUMIF(A1:A16,$D$1&"=10")
where D1 is absolutely refernced or
=SUMIF(A1:A16,D1&"=10")
if relative

--
Kevin Backmann

"Garver" wrote:

I am looking to use the conditional sum feature for summing a range of data
based on a criteria of course, but I am looking to be able to set the
criteria to a cell value so that I can drag the conditional sum to other
cells and have it calcuate accordingly. Any ideas? - Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -