Question of Complex IF statement

menor59

Active Member
Joined
Oct 3, 2008
Messages
495
Hello all. I hope u had a great 4th..

So heres my Code:
Code:
=IF(G3<>"",IF(COUNTIF(Prowatch!G:G,G3),(D3)&" IS ACTIVE",(D3)&" IS NOT ACTIVE (Update Owner)"),"")

So G3 Contains an Email Address...lets say abc@gmail.com

so in G3 IF abc@gmail.com is Found on Sheet Prowatch!G:G is then takes D3 and says: abc is ACTIVE, if its not found on prowatch!G:G it then says abc is not active (Update owner)

THIS WORKS perfectly...

but what i need to add is IF N/A@gmail.com is in G3 IGNORE.....


in Closing example:

G3 = N/A@gmail.com Readout IGNORE email is FIXED static it will alwayb be N/A
G3 = anything@gmail.com, Search Range on prowatch!G:G, if found value of D3 IS ACTIVE - email is dynamic...can be steve@gmail.com, or tom@gmail.com
G3 = someboddy@gmail.com, Search Range on prowatch!G:G if not found value of D3 IS NOT ACTIVE - email is dynamic can be jim@gmail.com

Any help would be appreciated
 
Last edited:

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Leandrial

Board Regular
Joined
Oct 9, 2012
Messages
78
Hi

im not sure i understand this correctly, so im trying with a mixed answer/question reply :)

But if i understand you correct, then the N/A@gmail weight the most... So wouldn't doing a series of If statements do the trick.. like




Code:
=IF(G3<>"", IF(G3="N/A@gmail.com", "IGNORE", IF(COUNTIF.... the rest of your original code
 
Last edited:

Leandrial

Board Regular
Joined
Oct 9, 2012
Messages
78
Code:
=IF(G3<>""; IF(G3="N/A@gmail.com"; "IGNORE"; IF(COUNTIF(Prowatch!G:G;G3);(D3)&" IS ACTIVE";(D3)&" IS NOT ACTIVE (Update Owner)"));"")
 

menor59

Active Member
Joined
Oct 3, 2008
Messages
495
Too many arguments

Rich (BB code):
=IF(Owners[@[EMAIL.1]]<>"",IF(Owners[@[OWNER.1]]="N/A@gmail.com", "IGNORE", IF(COUNTIF('Prowatch Import'!E:E,Owners[@[EMAIL.1]]),(Owners[@[OWNER.1]])&" IS ACTIVE",(Owners[@[OWNER.1]])&" IS NOT ACTIVE"),"")

You have ; those are acceptable?

ok I Did this"

Rich (BB code):
=IF(Owners[@[EMAIL.1]]<>"",IF(Owners[@[EMAIL.1]]="N/A@gmail.com", "IGNORE",IF(COUNTIF('Prowatch Import'!E:E,Owners[@[EMAIL.1]]),(Owners[@[OWNER.1]])&" IS ACTIVE",(Owners[@[OWNER.1]])&" IS NOT ACTIVE")))

So where N/A@gmail.com is the Cell says FALSE not Ignore
 
Last edited:

menor59

Active Member
Joined
Oct 3, 2008
Messages
495

ADVERTISEMENT

One More Scenerio. Sorry for the Inconvience again...

Rich (BB code):
=IF(Owners[@[EMAIL.1]]<>"", IF(Owners[@[EMAIL.1]]="N/A_Hiring@gmail.com",(Owners[@[OWNER.1]])&  " is IGNORED", IF(COUNTIF('Prowatch Import'!E:E,Owners[@[EMAIL.1]]),(Owners[@[OWNER.1]])&" IS ACTIVE",(Owners[@[OWNER.1]])&" IS NOT ACTIVE")),"")

I think i need an OR statement
currently I have in the code: IF(Owners[@[EMAIL.1]]="N/A@gmail.com",(Owners[@[OWNER.1]])& " is IGNORED"
if N/A_Hiring@gmail.com OR N/A_Facilities@gmail.com OR N/A_Reception@gmail.com
appear continue with the code...

Comments?
 
Last edited:

Leandrial

Board Regular
Joined
Oct 9, 2012
Messages
78
Too many arguments

Rich (BB code):
=IF(Owners[@[EMAIL.1]]<>"",IF(Owners[@[OWNER.1]]="N/A@gmail.com", "IGNORE", IF(COUNTIF('Prowatch Import'!E:E,Owners[@[EMAIL.1]]),(Owners[@[OWNER.1]])&" IS ACTIVE",(Owners[@[OWNER.1]])&" IS NOT ACTIVE"),"")

You have ; those are acceptable?

ok I Did this"

Rich (BB code):
=IF(Owners[@[EMAIL.1]]<>"",IF(Owners[@[EMAIL.1]]="N/A@gmail.com", "IGNORE",IF(COUNTIF('Prowatch Import'!E:E,Owners[@[EMAIL.1]]),(Owners[@[OWNER.1]])&" IS ACTIVE",(Owners[@[OWNER.1]])&" IS NOT ACTIVE")))

So where N/A@gmail.com is the Cell says FALSE not Ignore


It looks like the @owner.1 for the N/A@gmail.com has changed to @Email.1 in your last bit of code.. could that be why it says False??
 

Leandrial

Board Regular
Joined
Oct 9, 2012
Messages
78
Hi Menor


I must admit that i keep loosing track of your mail variables etc :)

I wont mind helping you more with this (if I can ofc) but i might very well need to c your actual workbook... So if it isn't sensitive information, can you post it for me to have a look and thereby have a better understanding of your dataset??


Best regards


Kasper
 

Forum statistics

Threads
1,136,341
Messages
5,675,206
Members
419,553
Latest member
hanahass

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
Top