Print: "In-Stock", or "No-Stock", IF Multiple Conditions Are Met

[BrandonBerner]

I'm back, with another headache haha.

I have 2 Sheets.

Sheet 1 - Is user-friendly
Sheet 2 - Is a database full of product information.


Sheet 1 currently looks like...

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][In Stock?][/TD]
[TD="align: center"][Product Name][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]Product #3[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]Product #2[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]Product #4[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]Product #1[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 1 should look like...

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][In Stock?][/TD]
[TD="align: center"][Product Name][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]No Stock[/TD]
[TD="align: center"]Product #3[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]In Stock[/TD]
[TD="align: center"]Product #2[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]In Stock[/TD]
[TD="align: center"]Product #4[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]No Stock[/TD]
[TD="align: center"]Product #1[/TD]
[/TR]
</tbody>[/TABLE]


Here are 2 NON-WORKING formula to explain what I'm trying to achieve...

IF B2=Sheet2!B2:B & If Sheet2!A2>=1, "In Stock", "No Stock"

=COUNTIFS((B2,Sheet2!B2:B),MATCH(Sheet2!A2>=1), "In Stock", "No Stock")

Sheet 2 looks like...

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][Inventory Count][/TD]
[TD="align: center"][Product Name][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]Product #1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]Product #2[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]Product #3[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]Product #4[/TD]
[/TR]
</tbody>[/TABLE]

 

[RasGhul]

Try this;

=IF(SUMIF(Sheet2!$B$2:$B$5,Sheet1!B2,Sheet2!$A$2:$A$5)>0,"In Stock","No Stock")

 

[BrandonBerner]

Try this;

=IF(SUMIF(Sheet2!$B$2:$B$5,Sheet1!B2,Sheet2!$A$2:$A$5)>0,"In Stock","No Stock")

That did the trick RasGhul!

Thanks for taking the time to help me solve this problem

 

[RasGhul]

You're welcome thanks for the feedback

 

[BrandonBerner]

Well....it WAS working... for my exact example above, but does not want to work for my actual workbook. -_-
...The headache starts again lol

I updated your formula according to this exact layout, and it wont work anymore.

Your Formula based on my previous example:

=IF(SUMIF(Sheet2!$B$2:$B$5,Sheet1!B2,Sheet2!$A$2:$A$5)>0,"In Stock","No Stock")

Updated Formula based on my actual worksheet:

[COLOR=#574123]=IF(SUMIF(Sheet2!$C$3:$C$11,Sheet1!C3,Sheet2!$B$3:$B$11)>0,"In Stock","No Stock")[/COLOR]

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=08A9FF]#08A9FF[/URL] , colspan: 5, align: center"]Product Request (Sheet #1)[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] , align: center"]STATUS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] , align: center"]Not Important[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] , align: center"]Product Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] , align: center"]SKU[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7B7B7]#B7B7B7[/URL] , align: center"]Not Important[/TD]
[/TR]
[TR]
[TD="align: center"]?[/TD]
[TD][/TD]
[TD]Product #1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ceecff][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CEECFF]#CEECFF[/URL] [/URL] , align: center"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]?[/TD]
[TD][/TD]
[TD]Product #2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ceecff][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CEECFF]#CEECFF[/URL] [/URL] , align: center"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]?[/TD]
[TD][/TD]
[TD]Product #4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ceecff][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CEECFF]#CEECFF[/URL] [/URL] , align: center"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]?[/TD]
[TD][/TD]
[TD]Product #6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ceecff][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CEECFF]#CEECFF[/URL] [/URL] , align: center"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]?[/TD]
[TD][/TD]
[TD]Product #9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ceecff][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CEECFF]#CEECFF[/URL] [/URL] , align: center"]9[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 71px"><col width="67"><col width="205"></colgroup><tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=08A9FF]#08A9FF[/URL] , colspan: 3, align: center"]Inventory (Sheet #2)[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=434343]#434343[/URL] , align: center"]SKU[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=434343]#434343[/URL] , align: center"]Quantity[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=434343]#434343[/URL] , align: center"]Name[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]Product #1[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]Product #2[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]Product #3[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]400[/TD]
[TD="align: center"]Product #4[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]Product #5[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]600[/TD]
[TD="align: center"]Product #6[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]Product #7[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]800[/TD]
[TD="align: center"]Product #8[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]Product #9[/TD]
[/TR]
</tbody>[/TABLE]

On sheet 2, each cell is pulling information from a 3rd sheet. Would this be causing it not to work?
For example: On sheet 2, A3 has the formula... =Sheet3!$C1<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

 

[RasGhul]

It shouldn't matter what you reference from Sheet3 as long as Product #1 (Sheet3) is exactly the same as Product #1 Sheet1 with no trailing spaces.

It would be maybe more robust to use a formula to extract your SKU with Vlookup or Index & Match so you don't end up with #N/A or #Ref ? errors.

What error are you getting? If unsure if you can make a small sample of your workbook with the same structure I can look at it for you.