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.

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

<tbody>
</tbody>

 

[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