Help with formula

[capriella]

Hi,

I need help figuring out the formula for the following case, I need to keep track of my inventory so I have a base inventory to wich I need to add the quantity of what is produced in a day and substract the amount of what is sold that same day.

Inventory table

Code	Name	Qty On Hand	QTY Produced	QTY Sold
LCC	Raw Milk	10
LEF	Past. Milk	35

<tbody>
</tbody>

Production Table

Date	Lot Number	Code	Qty Produced	Labels
5/06	5628	LCC	7	X
5/07	5629	LCC	20	X
5/07	5630	LEF	34	X

<tbody>
</tbody>

Sales Tables

Date	Client	Code	Qty Sold	Sale Price
5/06	Rescue Ranch	LCC	10	$4
5/07	Rescue Ranch	LCC	10	$4
5/07	Ostara	LEF	20	$6

<tbody>
</tbody>

The Quantity On Hand is my current starting inventory. I need to search for the product codes in the other tables and add the values for qty produced and qty sold to fill in the remaining rows and the be able to add and substract these values to the Qty On hand value.

For example:

if the code in inventory is equal to the code in production, add the values of all equal products and print the total in the qty produced row on the inventory table.

if the code is inventory is equal to the code in production add the values of all equal products and print the total in the qty sold row on the inventory table.

Afterwards I will add and substract these values to the Qty On Hand amount.

I Appreciate your help!

Regards,

-Gabriela

 

[JPARKHURST]

Use a sum if. I made 3 tabs, each with one of your tables.

=SUMIF(Production!$C:$C,A2,Production!$D:$D)

HTH,

Jon

 

[capriella]

Thanks!

I didn't know how to get the sumif to go past the first value!

This was great help


-Gabriela

 

[JPARKHURST]

put the formula in, hit enter. drag the lower right corner down to cover all needed cells.

I will post again with more detailed instructions.

Also - if you have formula tips on, when you hit =sumif( it will begin to tell you waht you'r elooking for. In this case, the first =sumif(WHAT_COLUMN_TO_IDENTIFY, VALUE_TO_IDENTIFY, RANGE_TO_SUM)

HTH,

Jon