# Sum by color

#### isay

##### New Member
Book1
ABCDE
1Period ActivityEnding ActinvityTotal PATotal EA
2-------------------------------------------
3\$ 446,507.93\$ 446,507.93
4\$ 7,434.78\$ 7,434.78
5\$ (2,853,793.50)\$ (2,853,793.50)
6\$ -\$ -
7\$ 938.81\$ 938.81
8\$ 11.22\$ 11.22
9\$ -\$ -
10\$ -\$ -
11\$ 7,242.65\$ 7,242.65
12\$ 39,135.00\$ 39,135.00
13
14
15\$ -\$ -
16\$ -\$ -
17\$ -\$ -
18\$ -\$ -
19\$ -\$ -
20\$ -\$ -
21\$ -\$ -
22\$ -\$ -
23\$ -\$ -
24\$ -\$ -
25\$ -\$ -
26\$ -\$ -
27\$ -\$ -
Sheet1

Hi Firefytr...

I think my problem is a bit similar to ndbhatt. My code also retrieve a data from my source then inputted in my worksheet on the above format. I want to sum the amount of the Period Activity and Ending Activity by color in ColumG and ColumnH in Set A and Set B. I also dont know how many records will appear on my worksheet so I thought using:

for me to identify the used range in column A/B in set A/B then use the result address(cell range) in my sum function.

I have a function sumbycolor
=sumbycolor(cellrange,37,false)

to sum the amount of Period Activity and Ending Activity by color per set of my data.

But until now, I doesn't work. I am totally clueless on what are the next thing to do.

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### Zack Barresse

##### MrExcel MVP
Can you post exactly what it is that you have tried? Does the formula (SumByColor) work everywehre else? Can you get it to work at all? Where did you put it?

#### isay

##### New Member
Book2
ABCDEFGH
1CenterAccountAccount DescriptionBeginning BalancePeriod ActivityEnding BalanceSumsSum (YTD)
2---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3CN-AGENTS
480149420722ggggg\$ 2,736,177.83\$ 63,637.66\$ 2,799,815.49
5420723ggggg\$ 131,839.13\$ 4,222.34\$ 136,061.47
6420724ggggg\$ 59,691,864.64\$ 3,504,745.22\$ 63,196,609.86
7420733ggggg\$ 7,305,831.34\$ 446,507.93\$ 7,752,339.27
8440010ggggg\$ -\$ (2,853,793.50)\$ (2,853,793.50)
9441110ggggg\$ (1,742.29)\$ 938.81\$ (803.48)
10441120ggggg\$ (70.72)\$ 11.22\$ (59.50)
11441330ggggg\$ (60,393,653.92)\$ (3,278,079.77)\$ (63,671,733.69)
12448920ggggg\$ 7,202.78\$ -\$ 7,202.78
13461210ggggg\$ 492,882.60\$ 7,242.65\$ 500,125.25
14461230ggggg\$ 5,578,850.28\$ 291,259.15\$ 5,870,109.43
15461330ggggg\$ 608,360.62\$ 61,991.18\$ 670,351.80
16758141ggggg\$ 734,830.00\$ 39,135.00\$ 773,965.00
17758142ggggg\$ 128,567.00\$ 7,220.00\$ 135,787.00
18758143ggggg\$ 2,110,043.85\$ 44,386.34\$ 2,154,430.19
19758153ggggg\$ 461,875.15\$ 4,500.00\$ 466,375.15
20
21
22PRE-CEPA
2380630421101bbbbb\$ (2,690,235.81)\$ -\$ (2,690,235.81)
24421102bbbbb\$ 1,691,853.64\$ -\$ 1,691,853.64
25421103bbbbb\$ 475,364.14\$ -\$ 475,364.14
26421104bbbbb\$ 2,719.00\$ -\$ 2,719.00
27421106bbbbb\$ (125,578.28)\$ -\$ (125,578.28)
28421107bbbbb\$ 103,801.74\$ -\$ 103,801.74
29421108bbbbb\$ 35,934.26\$ -\$ 35,934.26
30421111bbbbb\$ (10,090,513.90)\$ -\$ (10,090,513.90)
Sheet1

#### isay

##### New Member
my code is this for the above result.

Sub StatUpdate()
Dim FileName As String
Dim FileNum As Integer
Dim S As Long
Dim strline As String
Dim CurVar As String
Dim PrevVar As String
Dim intColorIndex As String
Dim intCounter6 As Integer
Dim intCounter7 As Integer
Dim intCounter8 As Integer
Dim intCounter9 As Integer
Dim intcounter10 As Integer
Dim intFreeFileNumber As Integer

intFreeFileNumber = FreeFile

intCounter6 = 0
intCounter7 = 0
intCounter8 = 0
intCounter9 = 0
intcounter10 = 0

Open "D:\TBStatYTD\sourcefile\TB-USD-EDITED.txt" For Input As #intFreeFileNumber

Do While Not EOF(intFreeFileNumber)

Line Input #intFreeFileNumber, strline

If CurVar = "" Then
CurVar = PrevVar
End If

If CurVar = "080149" Then

intCounter6 = intCounter6 + 1
If intCounter6 = 1 Then
ActiveSheet.Cells(S, 1) = "CN-AGENTS"
ActiveSheet.Cells(S, 1).Interior.ColorIndex = 35
S = S + 1
ActiveSheet.Cells(S, 1) = CurVar
End If

Select Case Trim(Mid(strline, 22, [10]))

'START EXPORT
Case "441110", "441120", "441130", "441210", "441220", "441230", "441330"
ActiveSheet.Cells(S, 2) = Mid(strline, 22, 10)
ActiveSheet.Cells(S, 3) = Mid(strline, 45, 31)
ActiveSheet.Cells(S, 4) = Mid(strline, 76, 20)
ActiveSheet.Cells(S, 5) = Mid(strline, 95, 19)
ActiveSheet.Cells(S, 5).Interior.ColorIndex = 36
ActiveSheet.Cells(S, 6) = Right(strline, 18)
ActiveSheet.Cells(S, 6).Interior.ColorIndex = 36
S = S + 1
intCountPlus = intCountPlus + 1

'END EXPORT
'START IMPORT
Case "758141", "758142", "758143", "758153"
ActiveSheet.Cells(S, 2) = Mid(strline, 22, 10)
ActiveSheet.Cells(S, 3) = Mid(strline, 45, 31)
ActiveSheet.Cells(S, 4) = Mid(strline, 76, 20)
ActiveSheet.Cells(S, 5) = Mid(strline, 95, 19)
ActiveSheet.Cells(S, 5).Interior.ColorIndex = 40
ActiveSheet.Cells(S, 6) = Right(strline, 18)
ActiveSheet.Cells(S, 6).Interior.ColorIndex = 40
S = S + 1
'END IMPORT
End Select

End If

If CurVar = "080630" Then
intCounter7 = intCounter7 + 1
If intCounter7 = 1 Then
ActiveSheet.Cells(S, 1) = "PRE-CEPA"
ActiveSheet.Cells(S, 1).Interior.ColorIndex = 35
S = S + 1
ActiveSheet.Cells(S, 1) = CurVar
End If
Call AccountUpdate(strline, S)
End If

If CurVar = "080631" Then
intCounter8 = intCounter8 + 1
If intCounter8 = 1 Then
ActiveSheet.Cells(S, 1) = "CEPA-SOUTH"
ActiveSheet.Cells(S, 1).Interior.ColorIndex = 35
S = S + 1
ActiveSheet.Cells(S, 1) = CurVar
End If
Call AccountUpdate(strline, S)
End If

If CurVar = "080639" Then
intCounter9 = intCounter9 + 1
If intCounter9 = 1 Then
ActiveSheet.Cells(S, 1) = "CEPA-CENTRAL"
ActiveSheet.Cells(S, 1).Interior.ColorIndex = 35
S = S + 1
ActiveSheet.Cells(S, 1) = CurVar
End If
Call AccountUpdate(strline, S)
End If

If CurVar = "080640" Then
intcounter10 = intcounter10 + 1
If intcounter10 = 1 Then
ActiveSheet.Cells(S, 1) = "CEPA-NORTH"
ActiveSheet.Cells(S, 1).Interior.ColorIndex = 35
S = S + 1
ActiveSheet.Cells(S, 1) = CurVar
End If
Call AccountUpdate(strline, S)
End If
PrevVar = CurVar
Loop

Close #1
End Sub

Sub AccountUpdate(tempStrLine As String, i As Long)

Select Case Trim(Mid(tempStrLine, 22, [10]))
Case "421101", "421106", "421111", "421201", "441210", "441220", "441230", "441330"
ActiveSheet.Cells(i, 2) = Mid(tempStrLine, 22, 10)
ActiveSheet.Cells(i, 3) = Mid(tempStrLine, 45, 31)
ActiveSheet.Cells(i, 4) = Mid(tempStrLine, 76, 20)
ActiveSheet.Cells(i, 5) = Mid(tempStrLine, 95, 19)
ActiveSheet.Cells(i, 5).Interior.ColorIndex = 36
ActiveSheet.Cells(i, 6) = Right(tempStrLine, 18)
ActiveSheet.Cells(i, 6).Interior.ColorIndex = 36
i = i + 1

Case "758041", "758042", "758043", "758052", "758053"
ActiveSheet.Cells(i, 2) = Mid(tempStrLine, 22, 10)
ActiveSheet.Cells(i, 3) = Mid(tempStrLine, 45, 31)
ActiveSheet.Cells(i, 4) = Mid(tempStrLine, 76, 20)
ActiveSheet.Cells(i, 5) = Mid(tempStrLine, 95, 19)
ActiveSheet.Cells(i, 5).Interior.ColorIndex = 40
ActiveSheet.Cells(i, 6) = Right(tempStrLine, 18)
ActiveSheet.Cells(i, 6).Interior.ColorIndex = 40
i = i + 1

End Select

End Sub

I am really sorry if my code is not code for I am still learning

Actually, I haven't use the the userange.address and sumfunction in my code because I am still on the testing process.

#### Zack Barresse

##### MrExcel MVP
Are you setting your Sum and Sum(YTD) via code? Are you wanting to? Or are you just looking to do this as a regular worksheet function entry?

Replies
1
Views
327
Replies
0
Views
432
Replies
7
Views
706
Replies
2
Views
560
Replies
1
Views
498

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,620
Messages
5,832,734
Members
430,160
Latest member
a_majda

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back