IF (A,B,C =0) .. IF (A=0, B>0) formulas

FPLA

New Member
Joined
Feb 22, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Good morning all

I'm trying to create some helper columns on a very large spreadsheet, to make decisions easier rather than staring at a lot of cells!

We have 8 sites, and for each I've a column "Stock, PO, Sales" (1 site doesn't have a sales column) Using reporting software/xlookups I am able to display these values.
The sheet has over 1,000 product lines... so I had the idea of helper columns;
1. No Stock | =IF(AND($G3=0,$J3=0,$M3=0,$O3=0,$R3=0,$U3=0,$X3=0,$AA3=0),"True","")
2. No Stock No PO |
=IF(AND($G3=0,$H3=0,$J3=0,$K3=0,$M3=0,$N3=0,$O3=0,$P3=0,$R3=0,$S3=0,$U3=0,$V3=0,$X3=0,$Y3=0,$AA3=0,$AB3=0),"True","")
3. No Stock No Sales | =IF(AND($G3=0,$I3=0,$J3=0,$L3=0,$M3=0,$N3=0,$O3=0,$Q3=0,$R3=0,$T3=0,$U3=0,$W3=0,$X3=0,$Z3=0,$AA3=0,$AC3=0),"True","")
4. No Stock Active PO
5. No Stock Active Sales
6. No Stock No PO Active Sales

1, 2 and 3 I've put the formula next to each that I'm currently running
*** Is the most effective and up to date formula (we use Office 365, fully up to date), if not what would be an ideal replacement

I'm stumped at working out 4, 5 and 6.


Appreciate any help!
Thanks, Luke
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Do you have headers to identify each column as Stock, Sales, or PO? If you do then you should be able to do this easier with SUMIFS.

Quickly glancing at your formulas, it looks like the site with no sales column is in the middle, which is likely to mess things up (formulas work best with consistent patterns). Would it be possible to add a sales column for that site even though it will never show any data, or is that going to mess things up with the reporting software that you mention?
 
Upvote 0
yes .. sorry I was trying to edit my post to make it clearer.. here's an example (5W = Sales column) I can add a Sales column for the other site and just hide in Excel or get the users to ignore it

1664365057919.png
 
Upvote 0
Perfect, I can work with that. Could I just check that the columns ending 5W are the sales figures? It seems obvious from the screen capture but assuming the obvious often leads to failure :oops:
I'm going to be offline for a couple of hours, I'll set up a quick test sheet and post a set of formulas for you when I get back.
 
Upvote 0
Perfect, I can work with that. Could I just check that the columns ending 5W are the sales figures? It seems obvious from the screen capture but assuming the obvious often leads to failure :oops:
I'm going to be offline for a couple of hours, I'll set up a quick test sheet and post a set of formulas for you when I get back.

No rush on this, I appreciate any help.. I've figured my way around A LOT with Excel formulas the last year or so but this has stumped me! Unfortunately I can't replicate an ERP report exactly in Excel so to me the helper columns are the key part for the users making decisions.

Assume that :) So 8 sites, and 3 columns each; Stock, PO, 5W
For my sheet, these are columns K through to AH.

here's a good example of one where I'm stuck
1664365771115.png

take the last 2 rows in that screenshot - none in stock, none on PO but we sold 1 and 47 from that site in the last 5 weeks. So I need "True" for 'No Stock No PO Active Sales' column against those rows.
 
Upvote 0
These should do what you need, I've done all 6 with your descriptions of the formulas you needed above them so that you can see what they relate to. Hopefully they will be reasonable easy to follow but please feel free to ask if you need me to explain anything. (If you click the little copy icon in the top left corner of the mini sheet below, you can paste it into excel and it will carry all of the formulas for you to save you doing them individually).

One thing to point out, I've assumed no negatives. If you could potentially have negative stock as a result of sales / orders exceeding available stock then I will need to make a few changes so that they are picked up correctly.

Book1
AJAKALAMANAO
11. No Stock2. No Stock No PO3. No Stock No Sales4. No Stock Active PO5. No Stock Active Sales6. No Stock No PO Active Sales
2TRUETRUETRUEFALSEFALSEFALSE
Sheet1
Cell Formulas
RangeFormula
AJ2AJ2=SUMIFS($K2:$AH2,$K$1:$AH$1,"*STOCK")=0
AK2AK2=AND(SUMIFS($K2:$AH2,$K$1:$AH$1,"* STOCK")=0,SUMIFS($K2:$AH2,$K$1:$AH$1,"* PO")=0)
AL2AL2=AND(SUMIFS($K2:$AH2,$K$1:$AH$1,"* STOCK")=0,SUMIFS($K2:$AH2,$K$1:$AH$1,"*5W")=0)
AM2AM2=AND(SUMIFS($K2:$AH2,$K$1:$AH$1,"* STOCK")=0,SUMIFS($K2:$AH2,$K$1:$AH$1,"* PO"))
AN2AN2=AND(SUMIFS($K2:$AH2,$K$1:$AH$1,"* STOCK")=0,SUMIFS($K2:$AH2,$K$1:$AH$1,"*5W"))
AO2AO2=AND(SUMIFS($K2:$AH2,$K$1:$AH$1,"* STOCK")=0,SUMIFS($K2:$AH2,$K$1:$AH$1,"* PO")=0,SUMIFS($K2:$AH2,$K$1:$AH$1,"*5W"))
 
Upvote 0
Solution
These should do what you need, I've done all 6 with your descriptions of the formulas you needed above them so that you can see what they relate to. Hopefully they will be reasonable easy to follow but please feel free to ask if you need me to explain anything. (If you click the little copy icon in the top left corner of the mini sheet below, you can paste it into excel and it will carry all of the formulas for you to save you doing them individually).

One thing to point out, I've assumed no negatives. If you could potentially have negative stock as a result of sales / orders exceeding available stock then I will need to make a few changes so that they are picked up correctly.

Book1
AJAKALAMANAO
11. No Stock2. No Stock No PO3. No Stock No Sales4. No Stock Active PO5. No Stock Active Sales6. No Stock No PO Active Sales
2TRUETRUETRUEFALSEFALSEFALSE
Sheet1
Cell Formulas
RangeFormula
AJ2AJ2=SUMIFS($K2:$AH2,$K$1:$AH$1,"*STOCK")=0
AK2AK2=AND(SUMIFS($K2:$AH2,$K$1:$AH$1,"* STOCK")=0,SUMIFS($K2:$AH2,$K$1:$AH$1,"* PO")=0)
AL2AL2=AND(SUMIFS($K2:$AH2,$K$1:$AH$1,"* STOCK")=0,SUMIFS($K2:$AH2,$K$1:$AH$1,"*5W")=0)
AM2AM2=AND(SUMIFS($K2:$AH2,$K$1:$AH$1,"* STOCK")=0,SUMIFS($K2:$AH2,$K$1:$AH$1,"* PO"))
AN2AN2=AND(SUMIFS($K2:$AH2,$K$1:$AH$1,"* STOCK")=0,SUMIFS($K2:$AH2,$K$1:$AH$1,"*5W"))
AO2AO2=AND(SUMIFS($K2:$AH2,$K$1:$AH$1,"* STOCK")=0,SUMIFS($K2:$AH2,$K$1:$AH$1,"* PO")=0,SUMIFS($K2:$AH2,$K$1:$AH$1,"*5W"))

Amazing, thank you. I've got a few SUMIFS elsewhere but never realised I could nest like this.
Just giving it a go now and will update ASAP.

No to negative thankfully!!!

Luke
 
Upvote 0
Thank you so much for the quick help on this. I had to make small tweaks (just on row/column - my fault) but sheet calculated and they all work as expected!
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,875
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top