Sum all rows containing text string VBA

[orsm6]

Hi guru's again i need some expert help....... with code i am trying to sum the contents of column O (beginning from O3) but to only sum it if column E = Z004, Z003, Z002 and CONV.

the hard part of this is.... the sum total needs to also go in column O, the sum total will not always be in the same row as the report size can change....what remains constant though is the sum total will ALWAYS be 5 columns to the right of a cell containing "total raw and pack cost"

i hope the table helps.

COGs runner.xlsm
	E				I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Total Manufactured Cost											Costing BoM (with Loss)
2	Material Type				Resource	Resource Text	Quantity	Base UoM	Price	Base Unit	Zero BoM	Component Scrap (%)	Operating Scrap (%)	Op. Scrap (Y/N)	+ Component Scrap	+ Operating Scrap	Quantity	Costing BoM
3	Z004				20032028		1010.1	EA	$301.00	1000	$304.04	1.01					2030.301	$611.12
4	Z004				20031708		6060.6	EA	$83.82	1000	$508.00	1.01					12181.806	$1,021.08
5	Z004				20031709		6060.6	EA	$47.25	1000	$286.36	1.01					12181.806	$575.59
6	Z004				PA021700		2040.8	EA	$7.55	1000	$15.41	2.04					6204.032	$46.84
7	Z004				PA027596		10.677	EA	$530.40	1000	$5.66	2.5					37.3695	$19.82
8	Z004				PA029751		6122.4	EA	$16.43	1000	$100.59	2.04					18612.096	$305.80
9	Z003				RA001003		-1.506	KG	$2,530.00	1000	-$3.81	0			0	0	-1.506	-$3.81
10	Z003				RA001003		1.506	KG	$2,530.00	1000	$3.81	0			0	0	1.506	$3.81
11	Z003				RA001965		382.6	KG	$3,026.66	1000	$1,158.00	1.6			612.16	0	994.76	$3,010.80
12	Z002				SA000450		563.185	KG	$3,987.05	1000	$2,245.45	13.71			7721.26635	0	8284.45135	$33,030.52
13	Z003				RA001003		-1.13	KG	$2,530.00	1000	-$2.86	0			0	0	-1.13	-$2.86
14	Z003				RA001003		1.13	KG	$2,530.00	1000	$2.86	0			0	0	1.13	$2.86
15	Z003				RA001011		0.836	KG	$13,650.00	1000	$11.41	11.98			10.01528	0	10.85128	$148.12
16	Z003				RA001030		34.196	KG	$4,407.00	1000	$150.70	12.57			429.84372	0	464.03972	$2,045.02
17	Z003				RA001256		1.259	KG	$32,499.34	1000	$40.92	12.12			15.25908	0	16.51808	$536.83
18	Z003				RA001260		2.514	KG	$2,450.00	1000	$6.16	11.98			30.11772	0	32.63172	$79.95
19	Z003				RA001672		7.064	KG	$397.80	1000	$2.81	12.6			89.0064	0	96.0704	$38.22
20	Z002				SA001212		1326.33	KG	$1,468.24	1000	$1,947.37	0.72			954.9576	0	2281.2876	$3,349.48
21						Total Raw & Pack Cost					$6,782.88
22
30	CONV					DVL1	8.381	HR	$5,537.87	100	$464.13	0						$464.13
31	CONV					FIX1	1500	KG	$1,013.25	10000	$151.99	0						$151.99
32	CONV					FMHR1	0.92	HR	$1,940.61	10	$178.54	0						$178.54
33	CONV					DVE1	1500	KG	$283.41	10000	$42.51	0						$42.51
34						Total Conversion Cost					$995.49
35																		Costing BoM
36						Total Manufactured Cost					$7,778.37
Layout

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K3:K100	Expression	=$K3="Unit Price"	text	NO
Q3:T100	Expression	=$E3="Z004"	text	NO
K3:K100	Expression	=$K3="Conversion"	text	NO
K3:K100	Expression	=$K3="Raw"	text	NO
K3:K100	Expression	=$K3="pack"	text	NO
V3:V100,A3:P100	Expression	=$E3="CONV"	text	NO
A3:V100	Expression	=$E3="Z002"	text	NO
A3:V100	Expression	=$E3="Z003"	text	NO
A3:V100	Expression	=$E3="Z004"	text	NO
A3:V100	Cell Value	contains "BoM"	text	NO
A3:V100	Cell Value	contains "Total"	text	NO

 

[DanteAmor]

How about:

Dante Amor
	E				I	J	K	L	M	N	O	P
1	Total Manufactured Cost											Costing BoM (with Loss)
2	Material Type				Resource	Resource Text	Quantity	Base UoM	Price	Base Unit	Zero BoM	Component Scrap (%)
3	Z004				20032028		1010.1	EA	301	1000	304.0401	1.01
4	Z004				20031708		6060.6	EA	83.82	1000	507.999492	1.01
5	Z004				20031709		6060.6	EA	47.25	1000	286.36335	1.01
6	Z004				PA021700		2040.8	EA	7.55	1000	15.40804	2.04
7	Z004				PA027596		10.677	EA	530.4	1000	5.6630808	2.5
8	Z004				PA029751		6122.4	EA	16.43	1000	100.591032	2.04
9	Z003				RA001003		-1.506	KG	2530	1000	-3.81018	0
10	Z003				RA001003		1.506	KG	2530	1000	3.81018	0
11	Z003				RA001965		382.6	KG	3026.66	1000	1158.00012	1.6
12	Z003				SA000450		563.185	KG	3987.05	1000	2245.44675	13.71
13	Z003				RA001003		-1.13	KG	2530	1000	-2.8589	0
14	Z003				RA001003		1.13	KG	2530	1000	2.8589	0
15	Z003				RA001011		0.836	KG	13650	1000	11.4114	11.98
16	Z003				RA001030		34.196	KG	4407	1000	150.701772	12.57
17	Z003				RA001256		1.259	KG	32499.34	1000	40.9166691	12.12
18	Z003				RA001260		2.514	KG	2450	1000	6.1593	11.98
19	Z003				RA001672		7.064	KG	397.8	1000	2.8100592	12.6
20	Z002				SA001212		1326.33	KG	1468.24	1000	1947.37076	0.72
21						Total Raw & Pack Cost					6782.88192
s1

Cell Formulas
Range	Formula
O21	O21	=SUM(SUMIFS($O$3:INDEX($O$3:$O$1000,MATCH("Total Raw & Pack Cost",$J$3:$J$1000,0)-1),$E$3:INDEX($E$3:$E$1000,MATCH("Total Raw & Pack Cost",$J$3:$J$1000,0)-1),{"Z002","Z003","Z004","CONV"}))

 

[orsm6]

How about:


Hi DanteAmor - thank you for the reply. Unfortunately i need to do this through VBA.... not a normal formula on the sheet.

thanks

 

[DanteAmor]

Try this

Sub sum_rows()
  Dim f As Range
  Set f = Range("J:J").Find("Total Raw & Pack Cost", , xlValues, xlWhole)
  If Not f Is Nothing Then
    f.Offset(, 5).Value = Evaluate("=SUM(SUMIFS(O3:O" & f.Row - 1 & ",E3:E" & f.Row - 1 & ",{""Z002"",""Z003"",""Z004"",""CONV""}))")
  End If
End Sub