Sum based on text

Panoos64 · Mar 29, 2019

[FONT=&quot]Hi all, I would like to “SUM” all figures from col. “C” to “O” in one row if in column “B” contains the word Unspecified using a VBA code. Therefore that the rows are not stably but rows 1 to 5 are the headings[/FONT][FONT=&quot]. I present below the original data and the expected result. Thank you all in advance[/FONT]

[FONT=&quot][/FONT]

[FONT=&quot]

Original data
[/FONT]
[FONT=&quot]

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
5	CODE	DESCRPTIO N	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC	TOTAL
125	60601-1	301 Unspecified	120.25	500.55	1500.21	1230.12	650.91	700.35		75.19	125.47	420.12	789.56	1300.45	7413.18
126	60604-1	304 Unspecified	200.45	600.25			567.89	835.12	1124.56	450.69		500.54		1110.78	5390.28
127	60608-1	308 Unspecified		650.13	1300.45	1200.98	440.78		458.75	1856.24	1320.78	1478.39	500		9206.5
128	60610-	Unspecified	350.12	780.45	800.45	780.12	800.45	500.78	450.25	355.65	256.85	654.23	450.78	358.96	6539.09

Expected result
[/FONT]
[FONT=&quot]

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

5

CODE

DESCRPTIO N

JAN

FEB

MAR

APR

MAY

JUN

JUL

AUG

SEP

OCT

NOV

DEC

TOTAL

125

60601-1

301 Unspecified

670.82

2531.38

3601.11

3211.22

2460.03

2036.25

2033.56

2737.77

1703.1

3053.28

1740.34

2770.19

28549.05

[/FONT]

mumps · Mar 29, 2019

Where do you want the expected results to appear? Do you want them on another sheet or at the bottom of the data?

Panoos64 · Mar 29, 2019

Hi mumps, i want the data in the same sheet and at bottom of data. Many thanks for your support

mumps · Mar 29, 2019

Try:

Code:

Sub GetTotals()
    Application.ScreenUpdating = False
    Dim LastRow As Long, x As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With Range("A5").CurrentRegion
        .AutoFilter 2, "=*Unspecified*"
        For x = 3 To 14
            Cells(LastRow + 1, x) = WorksheetFunction.Sum(Range(Cells(6, x), Cells(LastRow, x)).SpecialCells(xlCellTypeVisible))
        Next x
        .AutoFilter
        Range("O" & LastRow + 1) = WorksheetFunction.Sum(Range("C" & LastRow + 1 & ":N" & LastRow + 1))
        Range("B" & LastRow + 1) = "Unspecified Totals"
        Columns("B").AutoFit
    End With
    Application.ScreenUpdating = True
End Sub

Panoos64 · Mar 30, 2019

Thank you so much mumps, it works perfect and based to my data. Thanks also fro your time spent for my project. Hv a great day

mumps · Mar 30, 2019

You are very welcome.

Sum based on text

Panoos64

Well-known Member

Excel Facts

mumps

Well-known Member

Panoos64

Well-known Member

mumps

Well-known Member

Panoos64

Well-known Member

mumps

Well-known Member

Similar threads

Forum statistics

Share this page

Sum based on text

Panoos64

Well-known Member

Excel Facts

mumps

Well-known Member

Panoos64

Well-known Member

mumps

Well-known Member

Panoos64

Well-known Member

mumps

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock