Data Group Total

[Mandeep Dhami]

Hello,

I have a series of data in excel spreadsheet. I have 2 request to make:

1st Request:

Some of the fields in the spreadsheet are as follow:
Column A has Name of the Company
Column B has Invoice Number
Column C has Amount of Invoice

I want a formula in Column D, which will total up all the invoice amount
against each company.
For Example: If Company XYZ has 4 invoices then on cell D5 I should get the
total of C2:C5.

This way I have got hundreds of companies and each company has tens of
invoices.

2nd Request:

Out of the total amount arrived against each company in column D above I
want to arrange them as per the amount outstanding in the range of 10000 –
20000, 20001 – 30000 & 30001 – 40000. The arranged amount should have the
amount bifurcation as per their invoice number.

Hope to here some good solutions for the above query.

Cheers,
Mandeep

 

[Ardus Petus]

1st request)
Enter in D5:
=SUMIF(A2:A4,"XYZ",C2:C4)

2nd request)
Not clearly understood

HTH

 

[Marcelo]

Hi Mandeep

In my opinion the best way here is Pivot table, if you need you can have
tips on this web site
http://www.bettersolutions.com/excel/EUA153/VL115526332.htm

regards from Brazil
Marcelo


"Mandeep Dhami" escreveu:

 

[Mandeep Dhami]

Thanks AP for your solution.

I have tried your solution for the 1st request and it work only if the
companies name is XYZ (A2:A4) and there are 4 invoices (C2:C4).

In my database there are hundreds of companies each having 2-3-4-5 or any
number of invoices and what I wanted is that without changing formula again &
again I get the required result in column D.

Now 2nd request:

After we get the result in column D, I want to arrange the companies in
ascending order i.e., company XYZ has total $1000, another company ABC has
total $800, another company ERT has total $2000……………..so the arrangement
should be first company ERT then second company XYZ and then company ABC
should be arranged. This rearrangement of the company should also have the
invoice wise bifurcation so that I come to know how many invoices are
outstanding from each of the company ERT, XYZ, ABC, etc……

Cheers,
Mandeep