Sumif function

[GURURAJANR]

I have a petty grocery shop and since i also have a laptop, i wish to maintain the inventory and sales in excel, i have already designed the worksheets as below.
A B C D E F G H I J K L
Master Sheet1:Vlookup / Itemname / unit /Brand / supplier / costprice /salesprice/Openstock/Sales /Puchase/Damage/Closing stock

Sales Sheet2: A B C D E F G H I J K L M N
Date/ Itemname/Unit /Brand/supplier/costprice/salesprice/Qtysold/Discount/Final sales price/Opening Stk/Todaysales/purchase/Damage

I key in all the products in the respective columns, in Sheet 2! using Vlookup formula i managed to display the A,B,C,D,E,F,G columns. Now i need to enter the quantity sold(number of products) and arrive the final price. Now todays sales (clm:L) for each productsshould get appeared on the Master Sheet 1 in Sales column to calculate the closing stock. But the TodaySales(L) should match Itemname, Unit and Brand and sum the total number of particular product sold on that day. Same as Purchase column and damage column. I have used the below function in Sheet1:I column
Sumif(Sales!B&Sales!D&Sales!D,A1,L1:L10) but it does not fetch me the sum. Kindly help me to solve this issue.

Note: I have used =Concatenate(A,B,C) in A1, which gives me "Toordal1kgsafari". But when use the evaluation it shows every thing but not giving me the desired result. Please help me.

[CK76]

I'd suggest uploading sample workbook demonstrating your issue, along with manually inputted desired/expected result. It make it much easier for us to help you and test solutions.

FYI - Excel isn't really meant for inventory management, and while it is possible to do, it can quickly get out of hand and become more of hassle to maintain. I'd strongly recommend in investing (time and/or money) in actual database. Such as MS Access, MySQL, MS SQL Express, Postgre etc.

There are also free/open source inventory management/CRM out there that you can leverage.

[GURURAJANR]

It was very nice to see your reply and advise. Thankyou so much for your kind. Since it is very handy on the usage i am trying this.

I have also attached the file for your notice, i hope it should have a right function. Please help me.

[CK76]

Ah, ok. CONCATENATE isn't needed.

Formula for SLS (Column K) should use SUMIFS instead of SUMIF.
=SUMIFS(SALES!$O$2:$O$3,SALES!$C$2:$C$3,B2,SALES!$D$2:$D$3,C2,SALES!$E$2:$E$3,D2)

[GURURAJANR]

Hi...

It is working fine.

i thankyou with GODs feeling, coz, It is like asking someone whom nobody knows, who you are and when will you answer. In the fast moving world you have spent sometime to think and solve my problem. I am feeling blessed.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[GURURAJANR]

Hi...

You said in the previous post that Excel is not meant for Inventory system, then can we do it with Ms Access.

[humdingaling]

yes MS Access is definitely a step up from excel in terms of inventory management system
certainly it is much more scaleable