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

#### BrandonBerner

##### New Member
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...

 A B 1 [In Stock?] [Product Name] 2 ? Product #3 3 ? Product #2 4 ? Product #4 5 ? Product #1

<tbody>
</tbody>

Sheet 1 should look like...

 A B 1 [In Stock?] [Product Name] 2 No Stock Product #3 3 In Stock Product #2 4 In Stock Product #4 5 No Stock Product #1

<tbody>
</tbody>

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

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

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

Sheet 2 looks like...

 A B 1 [Inventory Count] [Product Name] 2 0 Product #1 3 20 Product #2 4 0 Product #3 5 40 Product #4

<tbody>
</tbody>

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### RasGhul

##### Well-known Member
Try this;

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

#### BrandonBerner

##### New Member
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

##### Well-known Member
You're welcome thanks for the feedback

#### BrandonBerner

##### New Member
Well....it WAS working... for my exact example above, but does not want to work for my actual workbook. -_-

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

Your Formula based on my previous example:
HTML:
``=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:
Code:
``[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>
 Product Request (Sheet #1) STATUS Not Important Product Name SKU Not Important ? Product #1 1 ? Product #2 2 ? Product #4 4 ? Product #6 6 ? Product #9 9

<tbody>
</tbody>

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
 Inventory (Sheet #2) SKU Quantity Name 1 0 Product #1 2 200 Product #2 3 0 Product #3 4 400 Product #4 5 0 Product #5 6 600 Product #6 7 0 Product #7 8 800 Product #8 9 0 Product #9

<colgroup><col style="width: 71px"><col width="67"><col width="205"></colgroup><tbody>
</tbody>

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

##### Well-known Member
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.

Replies
3
Views
111
Replies
0
Views
41
Replies
2
Views
140
Replies
12
Views
170
Replies
0
Views
278