Data Sorting

[H3MSY]

Please help.

Below is a very simplified version of my problem.

Table 1.
Product Type Location Price
A UK 10
A USA 5
B Eur 10
A UK 5
C Eur 1
C USA 2


The results I want, need to display as below.

Product Type Location Sum Price

A UK 15
A USA 5
A Eur 0
B UK 0
B USA 0
B Eur 10
C UK 0
C USA 2
C Eur 1

I have been playing around with DSUMS but was wondering if there was any other way. The real data will be in excess of 700 items

Thanks
Giles

[H3MSY]

hmmmm... The formatting didn't work out too well on the above message. Just to clarify..

Basically there are three columns.
1) Product Type (A,B & C)
2) Location (UK, USA & Eur)
3) Price

[Aladin Akyurek]

On 2002-04-30 06:50, H3MSY wrote:
hmmmm... The formatting didn't work out too well on the above message. Just to clarify..

Basically there are three columns.
1) Product Type (A,B & C)
2) Location (UK, USA & Eur)
3) Price

I assume in what follows that A1:C7 houses the sample data (including labels) you provided:

{"Product Type","Location","Price";
"A","UK",10;
"A","USA",5;
"B","Eur",10;
"A","UK",5;
"C","Eur",1;
"C","USA",2}

Activate A2.
Activate Data|Filter|Advanced Filter.
Check Copy to another location.
Enter $A$1:$B$7 for List range.
Enter $F$1 for Copy to.
Check Unique records only.
Activate OK.

Notice that column C is excluded from the List range.

In H1 enter: Sum Price

In H2 enter and copy down:

=SUMPRODUCT(($A$2:$A$7=F2)*($B$2:$B$7=G2),$C$2:$C$7)

[H3MSY]

Thanks for sorting this out for me, I have never really looked in the advanced filter option.

Thanks again,
Giles.