vba to add col C value if col A value contains

[Curtisyoung78]

Hi guys,

i am trying to write a vba to add values of col c (Weight) if the value in column a starts with a "W" and numbers after the "X" is less than 30 then add them all up and put the sum in cell H2(under Light). I've been working on this for a few days and cant quite figure it out, the syntax and "if then" i cant get quite right. can someone give me an example even, anything would help. thanks in advance.

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 1027"]
<colgroup><col><col><col><col span="9"></colgroup><tbody>[TR]
[TD="colspan: 3"]Area 10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]LIGHT[/TD]
[TD]MEDIUM[/TD]
[TD]HEAVY[/TD]
[TD]GRATING[/TD]
[TD]PLATE[/TD]
[/TR]
[TR]
[TD]L127x89x9.5[/TD]
[TD]71517[/TD]
[TD]1107[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PL-10[/TD]
[TD]N/A[/TD]
[TD]104[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PL-20[/TD]
[TD]N/A[/TD]
[TD]1120[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W150x22[/TD]
[TD]28777[/TD]
[TD]642[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W200x27[/TD]
[TD]38600[/TD]
[TD]1034[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W200x36[/TD]
[TD]8337[/TD]
[TD]297[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W200x42[/TD]
[TD]3100[/TD]
[TD]129[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W200x46[/TD]
[TD]12000[/TD]
[TD]554[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W250x49[/TD]
[TD]26801[/TD]
[TD]1316[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W310x39[/TD]
[TD]138000[/TD]
[TD]5340[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W310x45[/TD]
[TD]56500[/TD]
[TD]2522[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W310x52[/TD]
[TD]6000[/TD]
[TD]313[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W310x60[/TD]
[TD]110548[/TD]
[TD]6581[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W360x64[/TD]
[TD]26981[/TD]
[TD]1727[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total kg[/TD]
[TD]22,785[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

No need for macros, you can do that with a formula

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L
1	Area 10							LIGHT	MEDIUM	HEAVY	GRATING	PLATE
2	L127x89x9.5	71517	1107					1676
3	PL-10	N/A	104
4	PL-20	N/A	1120
5	W150x22	28777	642
6	W200x27	38600	1034
7	W200x36	8337	297
8	W200x42	3100	129
9	W200x46	12000	554
10	W250x49	26801	1316
11	W310x39	138000	5340
12	W310x45	56500	2522
13	W310x52	6000	313
14	W310x60	110548	6581
15	W360x64	26981	1727
asc

Cell Formulas
Range	Formula
H2	=SUMPRODUCT(--(LEFT($A2:$A15,1)="W"),--(RIGHT($A2:$A15,2)*1<30),$C2:$C15)

 

[Peter_SSs]

No need for macros, you can do that with a formula

That wouldn't work for values like
W200x100 (your formula would incorrectly include this row)
WPL-20 (your formula would incorrectly include this row)
W127x89x9.65 (your formula would incorrectly exclude this row)

 

[Peter_SSs]

This array formula could be tried. It should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.

{=SUM(IF(LEFT($A$2:$A$15,1)="W",IF(ISNUMBER(FIND("x",$A$2:$A$15)),IF(ISNUMBER(RIGHT(SUBSTITUTE($A$2:$A$15,"x",REPT(" ",20)),20)+0),IF(RIGHT(SUBSTITUTE($A$2:$A$15,"x",REPT(" ",20)),20)+0<30,$C$2:$C$15)))))}

 

[Fluff]

That wouldn't work for values like
W200x100 (your formula would incorrectly include this row)
WPL-20 (your formula would incorrectly include this row)
W127x89x9.65 (your formula would incorrectly exclude this row)

Excellent points Peter.

A VBA option

Sub SumWeight()
   Dim cl As Range
   Dim Tot As Double
   
   For Each cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      If Left(cl, 1) = "W" And Right(cl, Len(cl) - InStrRev(cl, "x")) < 30 Then
         Tot = Tot + cl.Offset(, 2)
      End If
   Next cl
   Range("H2").Value = Tot
End Sub

 

[Curtisyoung78]

Excellent points Peter.

A VBA option

Sub SumWeight()
   Dim cl As Range
   Dim Tot As Double
   
   For Each cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      If Left(cl, 1) = "W" And Right(cl, Len(cl) - InStrRev(cl, "x")) < 30 Then
         Tot = Tot + cl.Offset(, 2)
      End If
   Next cl
   Range("H2").Value = Tot
End Sub

Worked perfectly, thanks very much. Not just a solution to a problem but also an excellent learning opportunity.

 

[Curtisyoung78]

Worked perfectly. Thanks very much MrExcel MVP.

 

[Fluff]

Glad we could help & thanks for the feedback