Formula query - lookup function

[MattCM]

Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Please may you assist me with the below:
<o></o>
I have an excel 2003 spreadsheet with two worksheets.
<o></o>
Worksheet 1 has data (simplified):
<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:country-region w:st="on">Col.</st1:country-region> C <st1:City w:st="on">Product</st1:City>, <st1:country-region w:st="on">Col.</st1:country-region> I Cost, <st1:country-region w:st="on">Col.</st1:country-region> M Sale Price, <st1lace w:st="on"><st1:country-region w:st="on">Col.</st1:country-region></st1lace> P Gross Profit
Apple 10 10 0
Pear 10 20 10
Orange 10 25 15
Pear 15 15 0
Apple 10 17 7
Apple 15 20 5
<o></o>
What I am trying to create is Worksheet 2 which will analyse the data in Worksheet 1 to give some basic profitability figures and to inform purchasing.
As it stands Worksheet 2 is set out as:
<st1:country-region w:st="on">Col.</st1:country-region> A Product, <st1:country-region w:st="on">Col.</st1:country-region> B Total Sold, <st1:State w:st="on"><st1lace w:st="on">Col</st1lace></st1:State> C. Total Purchase Price, Col. C Avg Purchase Price, <st1:country-region w:st="on"><st1lace w:st="on">Col.</st1lace></st1:country-region> D Total Profit, Col. E Avg Profit
<o></o>
I am trying to get the formula’s to achieve the below
e.g Apple, 3, 35, 11.67, 12, 4
<o></o>
I have tried lookup functions but I am really struggling and would be very grateful of any assistance.
<o></o>
Thank you
<o></o>
Matthew

 

[TMShucks]

I'd suggest you have a look at Pivot Tables as the most flexible solution.

Otherwise, you could use an Advanced filter to extract the unique products ... though there are ways to do this with a formula ... then COUNTIF to count them and SUMIF to total the values. If you're using Excel 2007, you can use COUNTIFS, SUMIFS and AVERAGEIFS for multiple conditions.

 

[MattCM]

Thank you very much for that. I would be enormously grateful if you please could give me an example to get me started.

Thanks again

Matthew

 

[TMShucks]

Let's say the unique list is on the same sheet in column R.

Then:
=COUNTIF($C:$C,$R2)
=SUMIF($C:$C,$R2,I:I)
=SUMIF($C:$C,$R2,M:M)
=SUMIF($C:$C,$R2,P:P)

and copy down.

Use Advanced Filter to get the unique list in column R

Copy to another location.
List Range: $C:$C
Criteria Range: blank
Copy to: $R$1
Unique records only.

If you want to create the list and totals on a separate sheet, select that sheet before choosing Filter | Advanced Filter. The COUNTIF and SUMIF formula would also need to refer to the raw data sheet.