COUNTIF or SUMPRODUCT if adjecent CELL is not STRING

[Wobzy]

[FONT=&quot]Hi,[/FONT]
[FONT=&quot]Im trying to display the number of cells in a column containing "x" if adjecent column is not equal to "y" or "z" or "blank".[/FONT]
[FONT=&quot]Example;[/FONT]

	A (CONTRACT)	B (RE-CONTRACT)
1 (VALUE)	CURRENT ("y")	BLANK
2 (VALUE)	EXPIRED ("x")	CURRENT ("y")
3 (VALUE)	CURRENT ("y")	BLANK
4 (VALUE)	EXPIRED ("y")	EXTENDED ("z")

<tbody style="box-sizing: border-box; border: 0px; margin: 0px; padding: 0px; vertical-align: top; -webkit-font-smoothing: antialiased; text-size-adjust: none;">
</tbody>

[FONT=&quot]I know this isnt right but its the best way i can explain it.[/FONT]
[FONT=&quot]=COUNTIF(A1:A4,"EXPIRED",B1:B4,"<>CURRENT"or"<>EXTENDED"or"<>BLANK")+COUNTIF(B1:B4,"EXPIRED")[/FONT]
[FONT=&quot]End goal being a total for contracts not current. In this case 0.[/FONT]
[FONT=&quot]If any further clarification is needed please let me know.[/FONT]
[FONT=&quot]Thank you.[/FONT]

 

[Wobzy]

Omg now im getting mixed up with my active formula.

Nevermind im obviously speaking a foreign language.

Clearly too hard to understand i only want the sum of inactive values in the range in R, if the corresponding value in the range of T is not equal to active.

Delete this thread mods.

 

[snjpverma]

Give this a try if you want a count
=COUNTIFS(TRACKER!R:R,"*INACTIVE*",TRACKER!T:T,"<>*ACTIVE*")

 

[Wobzy]

Give this a try if you want a count
=COUNTIFS(TRACKER!R:R,"*INACTIVE*",TRACKER!T:T,"<>*ACTIVE*")

This is where i run into problems
I initally used the same logic but i have a number of variables for the inactive values.

Thus TRACKER!R:R,"*INACTIVE*" needs to fit muliple critera, 4 separate criteria need to be met.

EG; "*INACTIVE*"or"DECLINED"or"LIKELY"or"UNLIKELY"

 

[snjpverma]

are you sure the words you added now does not need asterisks around ?
IF so, then use this formula

=sum(COUNTIFS(TRACKER!R:R,{"*INACTIVE*","DECLINED","LIKELY","UNLIKELY"},TRACKER!T:T,"<>*ACTIVE*"))

or else

=sum(COUNTIFS(TRACKER!R:R,{"*INACTIVE*","*DECLINED*","*LIKELY*","*UNLIKELY*"},TRACKER!T:T,"<>*ACTIVE*"))

 

[Wobzy]

are you sure the words you added now does not need asterisks around ?
If so, then use this formula

=sum(countifs(tracker!r:r,{"*inactive*","declined","likely","unlikely"},tracker!t:t,"<>*active*"))

or else

=sum(countifs(tracker!r:r,{"*inactive*","*declined*","*likely*","*unlikely*"},tracker!t:t,"<>*active*"))

sum(countifs(

thankyou.

 

[Aladin Akyurek]

Not sure what you mean.

L21 is simply the total of active values in range R:R and T:T, as there is no disqualifier to an active value.

EG: =COUNTIF(TRACKER!R:R,"*ACTIVE*")+COUNTIF(TRACKER!T:T,"*ACTIVE*")

L22 needs to be the total of any values that are not active in range R:R if there is no active value in corresponding cell range T:T, + any values that are not active in range T:T.

Given the sample you posted, what numbers do you want to see in L21 and L22? That is/was the question...

 

[snjpverma]

Glad to see it worked for you

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules. Be sure to follow & read the link at the end of the rule too!

Cross posted at: COUNTIFS or SUMPRODUCT? Match criteria if other criteria is met.

If you do cross-post in the future and also provide a link, then there shouldn’t be a problem.

 

[Ingolf]

Give this a try if you want a count
=COUNTIFS(TRACKER!R:R,"*INACTIVE*",TRACKER!T:T,"<>*ACTIVE*")

Out of curiosity!!! How did you know, sheet is called TRACKER ????

 

[AliGW]

Ingolf - it's mentioned in the thread on the other forum.