Question on using text in formula

[Gaprofitt]

Hi All,

My wife and I run an Ice Cream business, we have an app that will allow us to export a lot of great data but I need to be able to run a macro/formula against it to calculate profit per item, inventory and total profit. An example is below.

The spreadsheet list out all the dates, selling price and a listing for each items name.
For example.

Cherry Screwball $1.50
Cherry Screwball $1.50
Chips Galore $3.00
Chips Galore $3.00
Chips Galore $3.00
Chips Galore $3.00


So I sold 2 Cherry Screwballs and 4 chips galore. Here is what I want to do for hundreds of items.

Each week I download a report of all the data, i'd like to run a macro or somehow easily add in the formulas to do the following:

I need to Calculate the QTY sold of an item off the text title " In other words "Chips Galore" =4. This will help us with inventory.

I need to Calculate the profit margin for each item, in other words, search for "Chips Galore" Chips Galore= 3.00 (Selling price) - 1.30 (Cost) = 1.70 (Profit)

Ultimately i'd have a cumulative total to know how much we profited versus what we sold and how much inventory we sold.

Any ideas, bare with me as i'm no excel pro.

Greg

[MarvinP]

Hi Gaprofitt and welcome to the forum.

This sounds like a job for Pivot Tables. http://www.ozgrid.com/Excel/excel-pivot-tables.htm

http://office.microsoft.com/en-us/ex...001034632.aspx