Conditional Sum with Empty Space or based on ordered series

[DickieD]

Hey all -- been a while

To keep this simple: let's say I have the following data:

NAME	SEG 1 ID	SEG 2 ID	VALUE 1	TOTAL
TOM	1	1	100
	1	2	200
	2	1	250
HARRY	1	1	200
	2	1	300
	2	2	1000
MIKE	1	1	50
	1	2	500
	2	1	200
	2	2	600

<tbody>
</tbody>

I want a formula for the "TOTAL" column that will sum the "VALUE 1" column for the "NAME" column values up through the next "NAME" entry... but will do so for *every* row in between (i.e. for Tom, I'd want "550" to appear on each of the first three "TOTAL" row lines). Here are the definitive parameters:

• Spaces between "names" will ALWAYS be empty
• The "Seg 1/2 ID" columns will ALWAYS be "1,1" on the line where there is a name, and will always be ordered (I'm including this fact in case it may be easier to use the ordered number series in those columns instead of the blank spaces in the name column)

So if there were something like "SUMIF(A2:A1000,OR(A2,[BLANK-CELL-IN-COLUMN-BETWEEN-A2-AND-NEXT-NON-BLANK-IN-COLUMN]),D2:D1000)" or something... that would be great. I'm sure there's some easy way to either index the column or make excel believe the blank spaces are all duplicates of whatever the most recent non-blank space was and return what I have above... I'm just not sure what that easy way is!

Thanks as always; holla

 

[hiker95]

Sorry, for some reason when I paste in your userID, it displayes in a strange way???? - like this: ****ieD


If the below screenshot is correct (cells, rows, columns), what should be the totals per row in column E?

Excel 2007
	A	B	C	D	E
1	NAME	SEG 1 ID	SEG 2 ID	VALUE 1	TOTAL
2	TOM	1	1	100
3		1	2	200
4		2	1	250
5	HARRY	1	1	200
6		2	1	300
7		2	2	1000
8	MIKE	1	1	50
9		1	2	500
10		2	1	200
11		2	2	600
12
Sheet1

 

[Aladin Akyurek]

Hey all -- been a while

To keep this simple: let's say I have the following data:

NAME	SEG 1 ID	SEG 2 ID	VALUE 1	TOTAL
TOM	1	1	100
	1	2	200
	2	1	250
HARRY	1	1	200
	2	1	300
	2	2	1000
MIKE	1	1	50
	1	2	500
	2	1	200
	2	2	600

<TBODY>
</TBODY>

I want a formula for the "TOTAL" column that will sum the "VALUE 1" column for the "NAME" column values up through the next "NAME" entry... but will do so for *every* row in between (i.e. for Tom, I'd want "550" to appear on each of the first three "TOTAL" row lines). Here are the definitive parameters:

• Spaces between "names" will ALWAYS be empty
• The "Seg 1/2 ID" columns will ALWAYS be "1,1" on the line where there is a name, and will always be ordered (I'm including this fact in case it may be easier to use the ordered number series in those columns instead of the blank spaces in the name column)

So if there were something like "SUMIF(A2:A1000,OR(A2,[BLANK-CELL-IN-COLUMN-BETWEEN-A2-AND-NEXT-NON-BLANK-IN-COLUMN]),D2:D1000)" or something... that would be great. I'm sure there's some easy way to either index the column or make excel believe the blank spaces are all duplicates of whatever the most recent non-blank space was and return what I have above... I'm just not sure what that easy way is!

Thanks as always; holla

Looks like...

E2, control+shift+enter, not just enter, and copy down:

=IF($A2="","",SUM(D2:INDEX(D2:$D$11,LOOKUP(9.99999999999999E+307,
  CHOOSE({1,2},ROWS(D2:$D$11),MATCH(TRUE,A3:$A$11<>"",0))))))

 

[DickieD]

Wow -- my notifications didn't go off for some reason.

Anyway... thanks as ALWAYS Aladin! That seems to work just greatly (to note, I was close this time -- was stuck in the middle though, where your use of "choose" does the trick!)



And yeah hiker... vBulletin doesn't like the first part of my name!

 

[Aladin Akyurek]

Wow -- my notifications didn't go off for some reason.

Anyway... thanks as ALWAYS Aladin! That seems to work just greatly (to note, I was close this time -- was stuck in the middle though, where your use of "choose" does the trick!)
...

You are welcome. Thanks for providing feedback.