Sum rows above me matching critiera...

[Guest]

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.

 

[Die_Another_Day]

=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

 

[Guest]

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]

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