Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum rows above me matching critiera...
What I'm trying to do is say, dynamically, that cell n is the sum of cell
values a-b where a-b are (1) in the same column as n, (2) match 2 or more specific cell values in the row n are in, (3) have a specific criteria, (4) are physically above cell n. The d* functions don't seem to be the right solutions because they rely on putting literals in the search criteria, which fits (3) but not (2). E.g., lets say my sheet looks like this row Project Feature Task Summary Size 1:"200","1000","9000","Sample","Task","1","50 2:"200","1000","9001","Sample","Task","2","30 3:"200","1000","Sample","Feature","1","70 4:"200","","","","Sample","Project","1","70 5:"201","1001","9001","Sample","task","2","30 6:"201","1001","9002","Sample","task","3","20 7:"201","1001","","Sample","Feature","1","50 8:"201","","","Sample","Project","2","50 e.g., the size value in row 8 is the sum of all the tasks with the same project number as the project number in row 8, but not including row 7 (which is a feature which sums up row 5 and 6). If I add a row between 13 and 14 with project 201, type = "task", I'd want its size to be automatically summed into row 8 without duplication. If it helps, I use the outline thing to group rows. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum rows above me matching critiera...
=Sum(If($C$2$C$10=C11,If($D$2$D$10=D11,If($E$2:$E$ 10="Sample",If(Row($C$2:$C$10)<Row(),$F$2:$F$10))) ))
Then press ctrl+shift+enter This says: Sum If Column C, Rows 2 - 10 = C11 And Column D, Rows 2 - 10 = C11 And Column E, Rows 2 - 10 = "Sample" And Row 2 - 10 < Row of formula cell HTH Charles Gary F wrote: What I'm trying to do is say, dynamically, that cell n is the sum of cell values a-b where a-b are (1) in the same column as n, (2) match 2 or more specific cell values in the row n are in, (3) have a specific criteria, (4) are physically above cell n. The d* functions don't seem to be the right solutions because they rely on putting literals in the search criteria, which fits (3) but not (2). E.g., lets say my sheet looks like this row Project Feature Task Summary Size 1:"200","1000","9000","Sample","Task","1","50 2:"200","1000","9001","Sample","Task","2","30 3:"200","1000","Sample","Feature","1","70 4:"200","","","","Sample","Project","1","70 5:"201","1001","9001","Sample","task","2","30 6:"201","1001","9002","Sample","task","3","20 7:"201","1001","","Sample","Feature","1","50 8:"201","","","Sample","Project","2","50 e.g., the size value in row 8 is the sum of all the tasks with the same project number as the project number in row 8, but not including row 7 (which is a feature which sums up row 5 and 6). If I add a row between 13 and 14 with project 201, type = "task", I'd want its size to be automatically summed into row 8 without duplication. If it helps, I use the outline thing to group rows. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum rows above me matching critiera...
Is there a way to just tell it all cells above that cell, otherwise, whenever
I add a row, I need to put the function in again by hand. e.g., lets' say the cell in question is N34. I want it to look in rows 1-33, rather than hand typing this stuff in each time and changing it if I add a row. I think I could do it with offset but I'm not sure how. Thanks "Die_Another_Day" wrote: =Sum(If($C$2$C$10=C11,If($D$2$D$10=D11,If($E$2:$E$ 10="Sample",If(Row($C$2:$C$10)<Row(),$F$2:$F$10))) )) Then press ctrl+shift+enter This says: Sum If Column C, Rows 2 - 10 = C11 And Column D, Rows 2 - 10 = C11 And Column E, Rows 2 - 10 = "Sample" And Row 2 - 10 < Row of formula cell HTH Charles Gary F wrote: What I'm trying to do is say, dynamically, that cell n is the sum of cell values a-b where a-b are (1) in the same column as n, (2) match 2 or more specific cell values in the row n are in, (3) have a specific criteria, (4) are physically above cell n. The d* functions don't seem to be the right solutions because they rely on putting literals in the search criteria, which fits (3) but not (2). E.g., lets say my sheet looks like this row Project Feature Task Summary Size 1:"200","1000","9000","Sample","Task","1","50 2:"200","1000","9001","Sample","Task","2","30 3:"200","1000","Sample","Feature","1","70 4:"200","","","","Sample","Project","1","70 5:"201","1001","9001","Sample","task","2","30 6:"201","1001","9002","Sample","task","3","20 7:"201","1001","","Sample","Feature","1","50 8:"201","","","Sample","Project","2","50 e.g., the size value in row 8 is the sum of all the tasks with the same project number as the project number in row 8, but not including row 7 (which is a feature which sums up row 5 and 6). If I add a row between 13 and 14 with project 201, type = "task", I'd want its size to be automatically summed into row 8 without duplication. If it helps, I use the outline thing to group rows. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum rows above me matching critiera...
Make the second part of the Array a relative reference instead of an
absolute: Change $C$2:$C$10 to $C$2:$C10 note you'll have to change all the absolute references. Charles Gary F wrote: Is there a way to just tell it all cells above that cell, otherwise, whenever I add a row, I need to put the function in again by hand. e.g., lets' say the cell in question is N34. I want it to look in rows 1-33, rather than hand typing this stuff in each time and changing it if I add a row. I think I could do it with offset but I'm not sure how. Thanks "Die_Another_Day" wrote: =Sum(If($C$2$C$10=C11,If($D$2$D$10=D11,If($E$2:$E$ 10="Sample",If(Row($C$2:$C$10)<Row(),$F$2:$F$10))) )) Then press ctrl+shift+enter This says: Sum If Column C, Rows 2 - 10 = C11 And Column D, Rows 2 - 10 = C11 And Column E, Rows 2 - 10 = "Sample" And Row 2 - 10 < Row of formula cell HTH Charles Gary F wrote: What I'm trying to do is say, dynamically, that cell n is the sum of cell values a-b where a-b are (1) in the same column as n, (2) match 2 or more specific cell values in the row n are in, (3) have a specific criteria, (4) are physically above cell n. The d* functions don't seem to be the right solutions because they rely on putting literals in the search criteria, which fits (3) but not (2). E.g., lets say my sheet looks like this row Project Feature Task Summary Size 1:"200","1000","9000","Sample","Task","1","50 2:"200","1000","9001","Sample","Task","2","30 3:"200","1000","Sample","Feature","1","70 4:"200","","","","Sample","Project","1","70 5:"201","1001","9001","Sample","task","2","30 6:"201","1001","9002","Sample","task","3","20 7:"201","1001","","Sample","Feature","1","50 8:"201","","","Sample","Project","2","50 e.g., the size value in row 8 is the sum of all the tasks with the same project number as the project number in row 8, but not including row 7 (which is a feature which sums up row 5 and 6). If I add a row between 13 and 14 with project 201, type = "task", I'd want its size to be automatically summed into row 8 without duplication. If it helps, I use the outline thing to group rows. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Critiera Sum Array - One critiera to be between two numbers | Excel Worksheet Functions | |||
Matching Rows | Excel Discussion (Misc queries) | |||
Summing based on various critiera | Excel Worksheet Functions | |||
Countif With Critiera | Excel Discussion (Misc queries) | |||
matching multiple rows | Excel Programming |