How to sum with unique Invoice Number wise ?????

krunal123

Board Regular
Joined
Jun 26, 2020
Messages
177
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Untitled.jpg


# How to sum with unique Invoice Number wise ?????

- Pleased Solve this query for formula ......very usefully trick for sale bill fill.
like single invoice & two or more products in this sheet... problem is how to sum product based on unique Invoice No.

- Pleased Help !!!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Try using this, but you could also use a pivot table to do all of them

Book1
AB
2EXP-10120
3EXP-10110
4EXP-10110
5exp-10225
6exp-1025
7exp-10325
8exp-10375
940
Sheet1
Cell Formulas
RangeFormula
B9B9=SUMIF(A2:A8,"EXP-101",B2:B8)
 
Upvote 0
another option
InvoiceQtyInvoiceQty
EXP-10120EXP-10140
EXP-10110exp-10230
EXP-10110exp-103100
exp-10225
exp-1025
exp-10325
exp-10375

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Invoice"}, {{"Qty", each List.Sum([Qty]), type number}})
in
    Group
 
Upvote 0
Hi Krunal,

Here is another option,

VBA Code:
Sub sum_Value()

Dim dict As New Scripting.Dictionary
Dim i As Long

With dict
    For i = 2 To 8
        If Not .Exists(Cells(i, 1).Value) Then
            .Add Cells(i, 1).Value, Cells(i, 2).Value
        Else
            .Item(Cells(i, 1).Value) = .Item(Cells(i, 1).Value) + Cells(i, 2).Value
        
        End If
    Next


'Print values

Dim c As Range
For Each c In Range("D2:D4")
    c.Offset(, 1).Value = .Item(c.Value)

Next c


End With



End Sub
 
Upvote 0
Yet another option is to use Excel's built-in Subtotal feature (on the 'Data' ribbon tab)
20 10 24.xlsm
AB
1Invoice No.Qty
2EXP-10120
3EXP-10110
4EXP-10110
5EXP-101 Total40
6EXP-10225
7EXP-1025
8EXP-102 Total30
9EXP-10325
10EXP-10375
11EXP-103 Total100
12Grand Total170
Subtotal
Cell Formulas
RangeFormula
B5B5=SUBTOTAL(9,B2:B4)
B8,B11B8=SUBTOTAL(9,B6:B7)
B12B12=SUBTOTAL(9,B2:B10)



Data can then be shown fully expanded (choosing the 3 box at the top left)
1603539279560.png


.. or summarised (choosing the 2 box at the top left)
1603539301859.png
 
Upvote 0
Other very common, using a Pivot Table

Book1
ABCDEFG
1Invoice No.Qty
2EXP-10120Row LabelsSum of Qty
3EXP-10110EXP-10140
4EXP-10110EXP-10230
5EXP-10225EXP-103100
6EXP-1025Grand Total170
7EXP-10325
8EXP-10375
9
10
Sheet8
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,242
Members
452,623
Latest member
russelllowellpercy

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top