If Statement for selective brands

[Zakky]

Hi, All.
Is it possible to construct an IF statement for all brands except for the following: anything with ANZ CC in the description, Not XX Branded, Not Branded Yet and To be Branded? At the moment I'm filtering these brands and putting in n/a. If i could copy down the IF statement to show sales (Qty x Value) that would be great. Thanks!

 

[jahanzaibriaz]

Try this formula

=IF(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"ANZ CC",""),"Not XX Branded",""),"Not Branded Yet",""),"To be Branded","")=A2,B2*C2,"n/a")

 

[Zakky]

jahanzailbriaz, thank you for the prompt reply. Impressive! works 100%. Many thanks!

 

[kevin9999]

Slightly shorter formula:

Book1
	A	B	C	D
1	Brand	Qty	Value	Sales
2	Nike	42	2	84
3	Adidas	65	3	195
4	Puma ANZ CC	67	4	n/a
5	Not XX Branded	33	5	n/a
6	Diadora ANZ CC	45	6	n/a
7	Ellesee	67	7	469
8	Not branded Yet	76	8	n/a
9	Hola ANZ CC	99	9	n/a
10	To be Branded	15	10	n/a
Sheet1

Cell Formulas
Range	Formula
D2:D10	D2	=IF(SUMPRODUCT(--ISNUMBER(SEARCH({"ANZ","Branded"},A2)))>0,"n/a",B2*C2)

 

[Zakky]

Kevin9999, thank you for the prompt reply. Another impressive solution that works 100%. Many thanks!

 

[Peter_SSs]

Shorter again ..

23 11 30.xlsm
	A	B	C	D
1	Brand	Qty	Value	Sales
2	Nike	42	2	84
3	Adidas	65	3	195
4	Puma ANZ CC	67	4	n/a
5	Not XX Branded	33	5	n/a
6	Diadora ANZ CC	45	6	n/a
7	Ellesee	67	7	469
8	Not branded Yet	76	8	n/a
9	Hola ANZ CC	99	9	n/a
10	To be Branded	15	10	n/a
Zakky

Cell Formulas
Range	Formula
D2:D10	D2	=IF(COUNT(SEARCH({"ANZ","Branded"},A2)),"n/a",B2*C2)

 

[Zakky]

Peter_SSc, thank you for responding. Again, i'm impressed with the pool of talent on Mr Excel. Who needs AI when we have so much talent on Mr Excel.