COUNTIF or SUMPRODUCT if adjecent CELL is not STRING

Wobzy

Board Regular
Joined
Mar 25, 2017
Messages
54
[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]
 

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).
Care to post a 5 row real sample? Without x, y, and z if these are not part of your data...

Source sheet (label; TRACKER)
RSTU
3(12) Active01/03
4(12) Expired -C07/03(12) Active08/03
5(12) Expired -D09/03
6Decline09/03
7(6) Expired -C12/03(6) Expired -C13/03

<tbody>
</tbody>

Working sheet
KL
21ACTIVE
22INACTIVE

<tbody>
</tbody>

The aim is to calculate (L22) the number of contracts currently inactive (R3:R7),(T3:T7)

But only in the case of column R, only if there is no value (Active, Expired-C) for a continuing contract in column T

Column R is initial point of sale. Column T is recontract thereafter.

I hope thats making sense.
 
Upvote 0
What are values which would obtain in L21 and L22?

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.
 
Upvote 0
Your last sentence is not clear.

if T:T = active then value = 0
if R:R = inactive & T:T != active then value = 0
if R:R = inactive & T:T = active then value = 1

I need the sum of inactive entries in T:T & R:R, less the value of inactive entries in R:R if corresponding T:T entry value is active.
 
Upvote 0
DISREGARD LAST POST. Why is there no edit or delete post.

Your last sentence is not clear.

if T:T = inactive or is blank then value = 0
if R:R = inactive & T:T != active then value = 0
if R:R = inactive & T:T = active then value = 1

I need the sum of inactive entries in T:T & R:R, less the value of inactive entries in R:R if corresponding T:T entry value is active.[/QUOTE]
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

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