Check a Column value & sum another Column

[ppenn]

Hello, everyone
I have a spreadsheet, look like below, I want to check if colA(between rows) value is the same and then sum colB, eg: row 3 to 5 is the same, so sum(b3:b5), put in C3.
Any help will be appreciated.

col A col B
1 A0202231 -4.73
2 A0202232 135.09
3 A0202238 89.54
4 A0202238 -90.72
5 A0202238 -0.18
6 A0202243 -0.36
7 A0202282 22.6
8 A0202311 -0.12


Many thanks

Peter

 

[DrBowes]

put this formula in C2 and drag down

=if(a2=a1,"",countif(a:a,b2))

You could also create a pivot table for a quick separate summary.

 

[KenWright]

As long as your data is sorted on Col A, then you could use Daat / Subtotals to do all your summarising.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[ppenn]

Hi,
I have just tried with your fomula, it did not work, I am thinking of using VBA, to check if currentCell value in colA equal the next 1 or maybe 2,3, 4 ect, and then and total colB to appropriate colA, eg. colA3, colA4 & colA5 are the same, so total colB3:colB5 is -1.36. please help.
Thanks

Peter

 

[ppenn]

Thanks for all your help. But it isn't what I want to do.

I want check colA if there is a duplicate rows, like A3, A4 & A5 are duplicates, so sum B3+B4+b5, then delete Row4 & row 5, only keep Row3, and value of -1.36, I've been trying using something like, the code below it check & delete the duplicates and only keep one, but I cannot sum B3:B5 before deletes Row4 and Row 5, any suggestion.

Worksheets("Sheet1&quot.Range("A1&quot.Sort _
key1:=Worksheets("Sheet1&quot.Range("A1&quot
Set currentCell = Worksheets("Sheet1&quot.Range("A1&quot
Do While Not IsEmpty(currentCell)
Set nextCell = currentCell.Offset(1, 0)
If nextCell.Value = currentCell.Value Then
currentCell.EntireRow.Delete
End If
Set currentCell = nextCell
Loop

thanks
Peter

 

[GlennUK]

Take DrBowes suggestion and try a pivottable.


Glenn.

 

[KenWright]

Data / Subtotals!!

Then just collapse the subtotals, select the data, do Edit / Go To Visible cells only, copy and paste elsewhere.

This function was designed for what you are trying to achieve.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------