Conditional Sum - dragging

[Garver]

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?

 

[Pete_UK]

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

 

[Kevin B]

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

 

[Garver]

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?

 

[Pete_UK]

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

 

[Garver]

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

Garver

 

[Pete_UK]

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

Pete

 

[brunrog]

Pete you helped me too

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

Pete