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

[FPLA]

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

 

[jasonb75]

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?

 

[FPLA]

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

 

[jasonb75]

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
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.

 

[FPLA]

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
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

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.

 

[jasonb75]

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
	AJ	AK	AL	AM	AN	AO
1	1. No Stock	2. No Stock No PO	3. No Stock No Sales	4. No Stock Active PO	5. No Stock Active Sales	6. No Stock No PO Active Sales
2	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE
Sheet1

Cell Formulas
Range	Formula
AJ2	AJ2	=SUMIFS($K2:$AH2,$K$1:$AH$1,"*STOCK")=0
AK2	AK2	=AND(SUMIFS($K2:$AH2,$K$1:$AH$1,"* STOCK")=0,SUMIFS($K2:$AH2,$K$1:$AH$1,"* PO")=0)
AL2	AL2	=AND(SUMIFS($K2:$AH2,$K$1:$AH$1,"* STOCK")=0,SUMIFS($K2:$AH2,$K$1:$AH$1,"*5W")=0)
AM2	AM2	=AND(SUMIFS($K2:$AH2,$K$1:$AH$1,"* STOCK")=0,SUMIFS($K2:$AH2,$K$1:$AH$1,"* PO"))
AN2	AN2	=AND(SUMIFS($K2:$AH2,$K$1:$AH$1,"* STOCK")=0,SUMIFS($K2:$AH2,$K$1:$AH$1,"*5W"))
AO2	AO2	=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"))

 

[FPLA]

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
	AJ	AK	AL	AM	AN	AO
1	1. No Stock	2. No Stock No PO	3. No Stock No Sales	4. No Stock Active PO	5. No Stock Active Sales	6. No Stock No PO Active Sales
2	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE
Sheet1

Cell Formulas
Range	Formula
AJ2	AJ2	=SUMIFS($K2:$AH2,$K$1:$AH$1,"*STOCK")=0
AK2	AK2	=AND(SUMIFS($K2:$AH2,$K$1:$AH$1,"* STOCK")=0,SUMIFS($K2:$AH2,$K$1:$AH$1,"* PO")=0)
AL2	AL2	=AND(SUMIFS($K2:$AH2,$K$1:$AH$1,"* STOCK")=0,SUMIFS($K2:$AH2,$K$1:$AH$1,"*5W")=0)
AM2	AM2	=AND(SUMIFS($K2:$AH2,$K$1:$AH$1,"* STOCK")=0,SUMIFS($K2:$AH2,$K$1:$AH$1,"* PO"))
AN2	AN2	=AND(SUMIFS($K2:$AH2,$K$1:$AH$1,"* STOCK")=0,SUMIFS($K2:$AH2,$K$1:$AH$1,"*5W"))
AO2	AO2	=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

 

[jasonb75]

never realised I could nest like this.

You can nest almost anything as long as the correct data type is returned.

 

[FPLA]

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!