Filtering Data and Sum the Values in the Adjacent Column

madhuchelliah

Board Regular
Joined
Nov 22, 2017
Messages
153
Hello Folks, i am preparing the sales report of books. I want to filter a book name in column C and want to add the number of books sold. the result should be in next sheet column C book name and column D total sold quantity. Please see the example below.

C
D
Book A
1
Book B
3
Book C
4
Book A
5
Book C
2
Book A
6
Book C
2
Book B
1
Book D
5

<tbody>
</tbody>
C
D
Book A
12
Book B
4
Book C
8
Book D
5

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,282
use PivotTable and put it on sheet2 or anywhere you want

BookSoldBookSum of Sold
Book A
1​
Book A
12​
Book B
3​
Book B
4​
Book C
4​
Book C
8​
Book A
5​
Book D
5​
Book C
2​
Grand Total
29
Book A
6​
Book C
2​
Book B
1​
Book D
5​
 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,282
I do not see any mention of vba in the title or post

have a nice day
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,524
Office Version
365
Platform
Windows
How about
Code:
Sub madhuchelliah()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      .comparemode = 1
      For Each Cl In Range("C2", Range("C" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = .Item(Cl.Value) + Cl.Offset(, 1).Value
      Next Cl
      Sheets("sheet2").Range("C2").Resize(.Count, 2).Value = Application.Transpose(Array(.Keys, .Items))
   End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,098,860
Messages
5,465,116
Members
406,414
Latest member
Discorz

This Week's Hot Topics

Top