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

[krunal123]

# 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 !!!

 

[Michael M]

Try using this, but you could also use a pivot table to do all of them

Book1
	A	B
2	EXP-101	20
3	EXP-101	10
4	EXP-101	10
5	exp-102	25
6	exp-102	5
7	exp-103	25
8	exp-103	75
9		40
Sheet1

Cell Formulas
Range	Formula
B9	B9	=SUMIF(A2:A8,"EXP-101",B2:B8)

 

[sandy666]

another option

Invoice	Qty		Invoice	Qty
EXP-101	20		EXP-101	40
EXP-101	10		exp-102	30
EXP-101	10		exp-103	100
exp-102	25
exp-102	5
exp-103	25
exp-103	75

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Invoice"}, {{"Qty", each List.Sum([Qty]), type number}})
in
    Group

 

[Mallesh23]

Hi Krunal,

Here is another option,

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

 

[Peter_SSs]

Yet another option is to use Excel's built-in Subtotal feature (on the 'Data' ribbon tab)

20 10 24.xlsm
	A	B
1	Invoice No.	Qty
2	EXP-101	20
3	EXP-101	10
4	EXP-101	10
5	EXP-101 Total	40
6	EXP-102	25
7	EXP-102	5
8	EXP-102 Total	30
9	EXP-103	25
10	EXP-103	75
11	EXP-103 Total	100
12	Grand Total	170
Subtotal

Cell Formulas
Range	Formula
B5	B5	=SUBTOTAL(9,B2:B4)
B8,B11	B8	=SUBTOTAL(9,B6:B7)
B12	B12	=SUBTOTAL(9,B2:B10)

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

.. or summarised (choosing the 2 box at the top left)

 

[hernantorres23]

Other very common, using a Pivot Table

Book1
	A	B	C	D	E	F	G
1	Invoice No.	Qty
2	EXP-101	20			Row Labels	Sum of Qty
3	EXP-101	10			EXP-101	40
4	EXP-101	10			EXP-102	30
5	EXP-102	25			EXP-103	100
6	EXP-102	5			Grand Total	170
7	EXP-103	25
8	EXP-103	75
9
10
Sheet8

 

[Michael M]

As suggested in Post # 2 ??