Column advice totalling data on criteria

[darkdestroyer1980]

Hi,

I need some general pointers on a couple of Excel macros that I'm trying to write. I have several columns of long data such as below:

a 65,395,297
b 32,180,320
n -36,603,762
o -37,355,582
p 71,138,303
q -78,920,800
r 220,522,039

-236,329,886

a1 -3,983,788
a2 -33,132,163
a5 86,851,363
a6 -105,765,873

-353,375,008

What I need to do is total the negative values in the column to give a total negative, as well as total the positive values in the column to give a total positive. However I need to miss out the subtotals for each section, the only way I can think of doing this is to look at column A and if no corresponding value then to ignore, but I'm not sure how to do a column check for if A is empty. The other thing I'm not sure about is how to keep a running total.

Any pointers please let me know.

Thx

 

[Bong]

Why not just use sumif on the spreadsheet (no VBA)?

If there's a reason you need to use a macro, I think the logic would be something like this:
[ol][li]find the first row of interest in column A[/li]
[li]find the last contiguous row with data in column A[/li]
[li]sum the positive values in the determined range[/li]
[li]sum the negative values in the determined range[/li]
[li]find the next row with data in column A[/li]
[li] ...etc...[/li][/ol]

So a possible implementation:
[ol][li]Does the data start in row 1?
If not, r1=sheet1.cells(1,1).end(xlDown).row[/li]
[li]r2=sheet1.cells(r1,1).end(xlDown).row[/li]
[li]set rngA=sheet1.Range(cells(r1,2),cells(r2,2))
s1=application.worksheetfunction.sumif(rngA,">0")[/li]
[li]s2=application.worksheetfunction.sumif(rngA,"<0")[/li]
[li]r1=sheet1.cells(r2+1,1).end(xlDown).row[/li][/ol]

_________________
Bob Rashkin

 

[nath]

Hello,

If you want to use Excel function you have ISBLANK() and SUMIF you can look into.

Personnaly I always prefer to work directly in VBA, i.e. create a procedure to do the work. in this case, you loop through your column, keeping a running positive total and a running negative total and checking column a before adding. The code would look like:

Sub RunningTotals()
Dim nPositif As Long, nNegatif As Long
Dim i As Integer
'there is diferent ways to check for the number of loop, as you don't mention about it I will just use a constant, let's say 1000, but come back to us if you want to know more
'I assume your data are in column B and the check cell in column A
Dim curVal As Long

For i = 1 To 1000
If Cells(i, 1).Value <> "" Then
curVal = Cells(i, 2).Value
If curVal < 0 Then
nNegatif = nNegatif + curVal
Else
nPositif = nPositif + curVal
End If
End If
Next i

Cells(1, 4).Value = "Neg Val: " & nNegatif
Cells(2, 4).Value = "Pos Val: " & nPositif
End Sub

Hope this helps

Nate

 

[AnotherHiggins]

I'd use SumProduct (again without VBA), which easily allows for multiple criteria:

Negative Sum:
[tab][COLOR=blue white]=SUMPRODUCT((A1:A50 <> "") * (B1:B50 < 0) * (B1:B50))[/color]

Positive Sum:
[tab][COLOR=blue white]=SUMPRODUCT((A1:A50 <> "") * (B1:B50 > 0) * (B1:B50))[/color]

Are the totals going on the worksheet? If so, just record a macro to put these formulas into the target cells.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Hi,

As a related issue, let me suggest putting column totals ABOVE the data.

Why make your users hunt to find it important aggregation?

You constantly have to adjust the location, otherwise.

I also suggest column Headings.

Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]