Adding numbers...

[Pwel]

First excuse my english, it's not my first language...

My problem is that I have a sheet with thousands of lines like those (I used "_" to align my column because space are not working) :

CLIENTS PRODUCT QTY TOTAL

Roger __ product1 __ 2 __ 080$
Roger __ product1 __ 3 __ 120$
Lynda __ product1 __ 1 __ 040$
Lynda __ product2 __ 1 __ 050$
Lynda __ product3 __ 1 __ 040$
Lynda __ product3 __ 2 __ 080$

and I need to add quantity and total for each time the client and the product is the same to obtain something like this :

CLIENTS PRODUCT QTY TOTAL

Roger __ product1 __ 5 __ 200$
Lynda __ product1 __ 1 __ 040$
Lynda __ product2 __ 1 __ 050$
Lynda __ product3 __ 3 __ 120$


I didn't find a way yet to do that...and like I said I have thousands of lines on my sheet so I have to find a way to do this with a function, a macro or something like that. By the way I'm using excel 2002.

If someone can help me please,

Thanks

[Ron Coderre]

I think I'd use a Pivot Table for that.

Select the data range
Data>Pivot Table
-->Select Excel range....click[Next]
-->Make sure the range is correct.... click [Next]
-->Select either New Worksheet or Existing Worksheet
-->Click the [Layout...] button
---->ROWs: Clients, Products
---->DATA: Sum of Qtr, Sum of Total....Click [OK]
Click [Finish]

Is that something you can work with?

Regards,
Ron

[Dave O]

I re-created your data with Clients, Product, Qty, Total in cells A1 to
D1, and the Roger-Lynda-product-qty-$ data in A2 to D7. Then I created
a summary section with the headers in G1 to J7 that looks like this:
Client__Product__Qty__Total
Roger___product1_5___200
Roger___product2_0___0
Roger___product3_0___0
Lynda___product1_1___40
Lynda___product2_1___50
Lynda___product3_3___120

(Great idea to use underscores, btw!)

I used this formula in I2 to sum the product quantity by client:
=SUMPRODUCT(--(G2=$A$1:$A$10),--(H2=$B$1:$B$10),$C$1:$C$10)

....and this formula in J2 to sum dollars by Client by Product:
=SUMPRODUCT(--(G2=$A$1:$A$10),--(H2=$B$1:$B$10),$D$1:$D$10)

Copy those formulas down through J7. Also please note that Excel
considers 200$ as a text string, not numbers; enter it as $200.

And no worries, your English is excellent!

[Pwel]

Thanks for both of you...

Ron Coderre your solution seems nice but I wasn't able to obtain the same layout for the result table as for my actual data table...

Dave O your solution is nice too but too complicated, the main reason why I want to add qtys and totals is to reduce the number of lines...there is hundreds of clients and products on my sheet...if I add lines for each products that the client didn't buy (with qty=0 and total=0) I'll have more lines then I have now on my sheet...

I'll try again to obtain the exact layout that I need using pivot table but if someone have another idea you're welcome

[Gary's Student]

You should consider using the Pivot Table feature that can be pulled-down
from Tools.

It is good for your application because it does not need any programming or
fancy formulae. It can summarize your data by both clients and product for
both total quantity and total dollars
--
Gary's Student


"Pwel" wrote:

>
> First excuse my english, it's not my first language...
>
> My problem is that I have a sheet with thousands of lines like those (I
> used "_" to align my column because space are not working) :
>
> CLIENTS PRODUCT QTY TOTAL
>
> Roger __ product1 __ 2 __ 080$
> Roger __ product1 __ 3 __ 120$
> Lynda __ product1 __ 1 __ 040$
> Lynda __ product2 __ 1 __ 050$
> Lynda __ product3 __ 1 __ 040$
> Lynda __ product3 __ 2 __ 080$
>
> and I need to add quantity and total for each time the client and the
> product is the same to obtain something like this :
>
> CLIENTS PRODUCT QTY TOTAL
>
> Roger __ product1 __ 5 __ 200$
> Lynda __ product1 __ 1 __ 040$
> Lynda __ product2 __ 1 __ 050$
> Lynda __ product3 __ 3 __ 120$
>
>
> I didn't find a way yet to do that...and like I said I have thousands
> of lines on my sheet so I have to find a way to do this with a
> function, a macro or something like that. By the way I'm using excel
> 2002.
>
> If someone can help me please,
>
> Thanks
>
>
> --
> Pwel
> ------------------------------------------------------------------------
> Pwel's Profile: http://www.excelforum.com/member.php...o&userid=26191
> View this thread: http://www.excelforum.com/showthread...hreadid=395006
>
>