Formula query - lookup function

MattCM

New Member
Joined
Mar 10, 2012
Messages
2
Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
Please may you assist me with the below:
<o:p></o:p>
I have an excel 2003 spreadsheet with two worksheets.
<o:p></o:p>
Worksheet 1 has data (simplified):
<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:eek:ffice: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, <st1:place w:st="on"><st1:country-region w:st="on">Col.</st1:country-region></st1:place> 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:p></o:p>
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"><st1:place w:st="on">Col</st1:place></st1:State> C. Total Purchase Price, Col. C Avg Purchase Price, <st1:country-region w:st="on"><st1:place w:st="on">Col.</st1:place></st1:country-region> D Total Profit, Col. E Avg Profit
<o:p></o:p>
I am trying to get the formula’s to achieve the below
e.g Apple, 3, 35, 11.67, 12, 4
<o:p></o:p>
I have tried lookup functions but I am really struggling and would be very grateful of any assistance.
<o:p></o:p>
Thank you
<o:p></o:p>
Matthew
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

TMShucks

Active Member
Joined
Jan 10, 2011
Messages
379
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

New Member
Joined
Mar 10, 2012
Messages
2
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

Active Member
Joined
Jan 10, 2011
Messages
379
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,763
Messages
5,626,727
Members
416,201
Latest member
brianhf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top