Addition Formula

[m0ws3r]

Hi,

I wondering if it possible to write a formula to count down a column, looking for the next occupied cell and the summing up the cells in another column.

Example:

Route	Delivery Sequence	Store Name	Store Reference	Depart Depot	Planned Cages	System Priority	System Trailer Number	DHL Trailer Number	Bay Number	Trailer Capacity	Route Planned Cages
	2	METRO CENTRE (3353)	3353		59
1	1	NEWCASTLE (1300)	1300	04:45	61	9500	STR1			58	0
	2	CHESHIRE OAKS (0399)	0399		19
2	1	CHESTER (1245)	1245	07:11	20	9400	STR2			54
	4	WARRINGTON (1517)	1517		90
	3	OSWESTRY SF (4255)	4255		65
	2	HESWALL SF (5827)	5827		38
3	1	PRESTATYN (0176)	0176	07:29	66	9300	STR3			58
4	1	GEMINI (2781)	2781	08:55	31	9200	STR4			58

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup>

So, what i essentially need is to look down column 'A' detect the next value, but then sum up the detail in column 'H'. Current Formula I have in column 'N' is =ISNUMBER(A3)*(SUM($H$2:H3))

Any help would be appreciated

Many Thanks
Mick

 

[Oeldere]

you need to sum the System Trailer Number (column H)?

What should be the result (manualy).

 

[steve the fish]

Something like?

=ISNUMBER(A3)*SUM(INDIRECT("$H$2:H"&ROW()))

 

[steve the fish]

Or:

=IF(ISNUMBER(A3),SUM(INDIRECT("$H$2:H"&ROW())),"")

?

 

[m0ws3r]

Hi Steve the fish,

returns the same

 

[steve the fish]

Hi. What returns the same? The two formulas? They are different. They may return the same however. The first will return 0 if A3 is not a number and the 2nd will return "". Or don't I understand what you are after?

 

[m0ws3r]

apologies, they return to same information as the formula I have,

what i need is the formula to detect the blanks up to a cell with a number in route column, then add the corresponding cells in H columns

example
Route 1 = sum of H2:H3 then count the next set of blank cells up until an occupied cell
Route 2 would be = sum of H4:H5
Route 3 would be = sum of H6:H9

 

[Peter_SSs]

Like this?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Route									Sum
2								1
3	1							3		4
4								2
5								4
6								5
7								3
8	2							2		16
9								4
10								3
11	3							2		9
12	4							1		1
13								1
14								2
15	5							3		6
Sum

 

[m0ws3r]

Peter,

there is a word for people like you......awesome

much appreciated

thanks
Mick

 

[Peter_SSs]

Glad it helped. Thank you for the generous feedback.