Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? * |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formating - dragging a cond. format over an entire col | Excel Worksheet Functions | |||
Dragging | Excel Worksheet Functions | |||
Dragging Conditional Formatting | Excel Worksheet Functions | |||
About this dragging... | Excel Discussion (Misc queries) | |||
dragging | Excel Discussion (Misc queries) |