Calculation

[maximas]

I'm trying to sum up the amounts only if the id# is the same. how can I do that? here is the data.

col1 col2 col3
id# Amt1 | Amt2
1 1 | 1
1 2 | 1
1 1 | 2
2 2 | 1
3 -2 | -1 <- negative amt
1 -1 | -1 <- negative amt
2 2 | 2
3 3 | 1
4 1 | 1


id# total
1 6
2 7
3 1
4 2

 

[AnotherHiggins]

No VBA needed:
=SUMIF(A:A,A2,B:B)+SUMIF(A:A,A2,C:C)

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

Help us help you. Please read FAQ181-2886 before posting.

 

[maximas]

how can there be no VBA is you need to compare the data

col1 | col2 | col3
id# | Amt1 | Amt2
1 | 1 | 1
1 | 2 | 1
1 | 1 | 2
2 | 2 | 1
3 | -2 | -1 <- negative amt
1 | -1 | -1 <- negative amt
2 | 2 | 2
3 | 3 | 1
4 | 1 | 1


id# | total
1 | 6
2 | 7
3 | 1
4 | 2

 

[maximas]

I'm calculate the sum only if the id#'s match each other.

 

[AnotherHiggins]

Did you try the formula?

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

Help us help you. Please read FAQ181-2886 before posting.

 

[DirkStruan]

Maximas

Is your data in an Excel spreadsheet?

If so, I agree with John: The formula should work and there is no need for VB - just don't use it underneath the table data as the reference to the entire columns of A-C would cause circular references. In that case, either limit the range in the formula or move the results table to column 4 or beyond.

You will of course need 4 copies of the formula (based on your example) referring to A2, A5, A6 & A10 (which equal 1, 2, 3, 4 respectively), or simply refer to the values themselves.

(John - apologies for butting in)

D

 

[maximas]

Thank you! And all this time I'm trying to loop thru the spreadsheet. I'll try and post the result. thanks for the help.

 

[SkipVought]

Of course, there's also the PivotTable wizard, which would generate the necessary totals for ALL the ID's in about

FIVE SECONDS

... no code or formulas.

Skip,

_[red][/red]