sumifs + table error

[rapitorres]

Hi. I'am currently working on a simple in out inventory using barcode scanning and im having difficulty in arranging things. mainly Im using sumifs and vlookup function to make this possible and Im stock. I hope someone can help with this.

i have created table with headers
this is my master list of products

and also created another sheet named "IN" and "OUT"
and they also have table name which is
intable for IN
outtable for OUT

now my formula in my master list "IN" header
=SUMIFS(INTABLE[IN],INTABLE[BARCODE],[@BARCODE])

same in OUT header
=SUMIFS(INTABLE6[OUT],INTABLE6[BARCODE],[@BARCODE])

now the problem lies within the actual "IN" and "OUT sheet

I've make a dummy product in the master list

and I used vlookup function to filter product

as per the image above. I entered "1" in the barcode header and details are listed as per masterlist.

and also I entered "1" in the IN header

and us per effect it will reflect on the master list it should have "1" on "IN" header

it will only take effect IF the position is the same. like if I entered details on MASTERLIST sheet in cell A4.
and I will enter it also in cell A4 in "IN" sheet and "OUT" sheet thats the only time it will reflect on masterlist sheet.

hope this makes sense.

 

[mamady]

Hi Dear,

I am a little bit confused. Which sheet is your input sheet and which show the results?

So, does the master list reads from both IN and OUT sheets, or vis-e-versa?
Can you please paste herewith the VLOOKUP formula?

Regards
M. Yusuf

 

[rapitorres]

Hi Dear,

I am a little bit confused. Which sheet is your input sheet and which show the results?

So, does the master list reads from both IN and OUT sheets, or vis-e-versa?
Can you please paste herewith the VLOOKUP formula?

Regards
M. Yusuf

"So, does the master list reads from both IN and OUT sheets"
> yes my input sheet is in and out sheet. but if i want to insert new product im listing it in my master list


thanks for replying

 

[rapitorres]

Above is vlookup for IN sheet

btw this is a mock up table. but same naming.

 

[mamady]

Hi Dear,

Sorry for the late reply, I will try to replicate your setup to see the produced issue.

Best Regards
M. Yusuf

 

[rapitorres]

Hi Dear,

Sorry for the late reply, I will try to replicate your setup to see the produced issue.

Best Regards
M. Yusuf

i really appreciate it thank you so much

keep safe.

 

[mamady]

Hi Dear,

So my understanding is a follows:

• You add any new product or Item in the masterlist, and then you go to IN and OUT sheets and do a VLOOLUP to list there the items with its details;
• Then you manually add value to the IN and OUT headers, in the IN and OUT sheet, so that the count is reflected in the masterlist under IN and OUT headers
• your problem is unless the data in the tables are aligned across all sheet, the SUMIF function don't read the information

Correct me please if my understanding is correct.

I created the same setup based on my above-mentioned understanding and noticed the following issues:

1. you should add @ in the lookup value to refer to the specific row rather than the range/table header, so the formula should be
=VLOOKUP([@BARCODE],MASTERLIST,2,FALSE)

2. Although you advise that the OUT table in the OUT sheet is named OUTTABLE, but the formula you shared shows that the name is INTABLE6, you need to make sure if it is reading from the correct source
=SUMIFS(INTABLE6[OUT],INTABLE6[BARCODE],[@BARCODE])

Kindly check the above against your setup, and let me know if it sorts out your issue.

Best Regards
M. Yusuf

 

[rapitorres]

Hi M.Yusuf

"So my understanding is a follows:

• You add any new product or Item in the masterlist, and then you go to IN and OUT sheets and do a VLOOLUP to list there the items with its details;
• Then you manually add value to the IN and OUT headers, in the IN and OUT sheet, so that the count is reflected in the masterlist under IN and OUT headers
• your problem is unless the data in the tables are aligned across all sheet, the SUMIF function don't read the information"

Exactly, You're correct with this.

OMG your correct its becuase im wrong with naming table I've use a different table name in my vlookup.
its working properly now. ! in and out header is not accepting values from in and out sheet. in any position in the workbook. as long vlookup is properly arrange
im glad that i learned how to use tables i dont need to copy codes to every cell. just automatically copies it.

thanks you've help me alot

PS: learning - proper naming convention

 

[mamady]

You are most welcome dear, I am glad that I was able to help