# Sum with Invoice no. wise

Hi Expert,

Need support to get Sum Qty with Invoice wise. Example Data is given below

Invoice No. - Articel Code - Qty. - Result require
INV123 - Toy - 50 - 80
INV123 - Book - 20
INV123 - pencil - 10
INV567 - Cake - 20 - 30
INV567 - Pen - 10

Could you guide me how i can use formula to retrive sum Qty data.

BR,
Sultan Khan

Please provide the results exactly as you are expecting to see them.

Noel

Hi Novel,

I require total sum of qty.

Result out came sould be =80 against invoice no. Inv123.
And
Inv567 =30

Invoice No. - Articel Code - Qty. - Result require
INV123 - Toy - 50 - 80
INV123 - Book - 20
INV123 - pencil - 10
INV567 - Cake - 20 - 30
INV567 - Pen - 10

BR
Sultan

This is as simple as creating a variable (I called mine Var Sum of Qty) with the following formulaâ€¦

=Sum([Qty])

You could also add a break on InvoiceNo and Sum the Qty column.

The results of both methods are shown below.

1 Like

Hi Novel,

This is possible through invoice break.
As per requirement is result should be next column without break invoice wise.

BR
Sultan

Alright. Create a new variable called Var InvoiceNo Sum of Qty defined as followsâ€¦

=Sum([Qty]) In ([InvoiceNo])

The key with this formula is that it will give you the total for each InvoiceNo on all rows. I know this isnâ€™t what you want, but I want to show the steps to get there.

Now add breaks on InvoiceNo and the new Var InvoiceNo Sum of Qty variable.

Almost there.

Next right-click on the table and choose â€śBreaks > Manage Breaksâ€¦â€ť. We do not want break headers or footers for either break. For InvoiceNo we want to choose â€śDisplay allâ€ť under Duplicate values and for Var InvoiceNo Sum of Qty we want to choose â€śDisplay firstâ€ť. They should look like thisâ€¦

Finally in the Format Table properties check the â€śTable headersâ€ť checkbox. It got unchecked behind the scenes when the breaks were added.

And there you have it.

Noel

1 Like

Dear Noel

I tried formula =[Var(Sum([Qty] In ([InvoiceNo]).

System is validate correct formula but all row showing total Qty instead of invoice Qty.

And =[Var Sum of Qty] In ([InvoiceNo]) is not working.

Pls guide, where I am going wrong.

BR
Sultan

I was making a faulty assumption. I have edited my previous response. The Var InvoiceNo Sum of Qty variable should have the following formulaâ€¦

=Sum([Qty]) In ([InvoiceNo])

Give that a try.

Hi Noel,

Sorry to say, but still showing total qty. On each row instead of invoice wise sum.

BR,
Sultan

Is the final table in my response above what you require?

If so, please provide screenshots to show your formulas, breaks, and format table properties as I did in my example and perhaps I can guide you to your objective.

Noel