Hi All,

I want to creat an inventory list in sheet 1, where I have hundreds of different goods`every day arrive in warehouse and goes out the same day or other days in a week. But in sheet 2 I want to have a summery of the remaining goods available in my warehouse to report.

Which formula should I use in sheet 2 that can search the same material name in say Column A sheet 1 but arrived in different time in a same day or week, and find the related value in stock in say column F respective rows, to have all added values of one material in one cell in sheet 2 automatically calculated.

Cheers Iraj.

You don't need anything more complicated than the SUMIF function:
=SUMIF(Sheet1!A$1:A$2500,Sheet2!A1,Sheet1!F$1:F$25 00)
where Sheet2!A1 holds the item description you want to get a tally for. The formula assumes data on Sheet1 rows 1-2500.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi Paul,

Thanks, I will try to see how does that work.

Regards Iraj.

Hi Paul,

Did not work that formula, I will write more specific as follows;

Col.A Material Col.C date Col.F arrive in Col.H dispatch out Col.J in stock
3/5/2013
Wood 15 10 5
Rice 20 15 5
sweets 10 5 5
cakes 12 4 8
fruits
3/6/2013
lumber 10 7 3
wood 15 5 10
balls 12 8 4
Rice 20 12 8
and so on
for other
days A:2500

The above values are under each column accordingly, to show how many arrived in , dispatch out and in stock for the related date.

Now I like to have in sheet2, Col.A all the material names and in Col B the respective cells which have formula to find eg. all woods in stock from diffent date and show the total in one cell as summery of that week or 10 weeks. Because when I write the repot I do want to have all in stocks added in one cell for that specific material name, and respectively for other goods are in stock too.

Regards Iraj.

It works fine for me - with your data. See attached

Attached Files

Demo.xls (24.5 KB, 14 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi Paul,

Yes, that's correct, appreciate for prompt response.

Sincerely Iraj.