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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Name[/TD]
[TD]Qty On Hand[/TD]
[TD]QTY Produced[/TD]
[TD]QTY Sold[/TD]
[/TR]
[TR]
[TD]LCC[/TD]
[TD]Raw Milk[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LEF[/TD]
[TD]Past. Milk[/TD]
[TD]35[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Production Table

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Lot Number[/TD]
[TD]Code[/TD]
[TD]Qty Produced[/TD]
[TD]Labels[/TD]
[/TR]
[TR]
[TD]5/06[/TD]
[TD]5628[/TD]
[TD]LCC[/TD]
[TD]7[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]5/07[/TD]
[TD]5629[/TD]
[TD]LCC[/TD]
[TD]20[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]5/07[/TD]
[TD]5630[/TD]
[TD]LEF[/TD]
[TD]34[/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]

Sales Tables

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Client[/TD]
[TD]Code[/TD]
[TD]Qty Sold[/TD]
[TD]Sale Price[/TD]
[/TR]
[TR]
[TD]5/06[/TD]
[TD]Rescue Ranch[/TD]
[TD]LCC[/TD]
[TD]10[/TD]
[TD]$4[/TD]
[/TR]
[TR]
[TD]5/07[/TD]
[TD]Rescue Ranch[/TD]
[TD]LCC[/TD]
[TD]10[/TD]
[TD]$4[/TD]
[/TR]
[TR]
[TD]5/07[/TD]
[TD]Ostara[/TD]
[TD]LEF[/TD]
[TD]20[/TD]
[TD]$6[/TD]
[/TR]
</tbody>[/TABLE]

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