Condition Formula

[kramart]

Hi,
I have this table ... this is a part of millions of records.

I have functions that show if STAT is 1 or 0, if type is E, if type is CA. This example shows that the same work 24188686 has one E and two CA. If I filter columns I can see either E or CA from the same work.

I would like to have some formula in the column COMBINE that would check in the first row : if type CA = 0 then check if there are CA's with the same WORKREF as E and if true return value 1.

The same approach for rows where E =0 to check if there are E's with the same WORKREF as CA and return value 1.

The idea is that then I can sum these four columns and filter column TOTAL to show all values =3.

WORKREF	STAT	TYPE	IF STATE =1	IF TYPE = E	IF TYPE = CA	COMBINE	TOTAL
24188686	1	E	1	1	0	1	3
24188686	1	CA	1	0	1	1	3
24188686	1	CA	1	0	1
445566	0	CA	0	0	1

<tbody>
</tbody>

Hope that make sense.

Any suggestions would be much appreciated!!!

Thanks,

A

 

[inactiveuserps07]

kramart, my intuition says that, whatever it is you're trying to do, there's a more efficient way. However, given the information you've provided, and assuming the upper left-hand corner of your table is in A1, copy the formula below to F2 and drag down Column F as far as you need:

=IF(F2=0,(COUNTIFS(A:A,A2,C:C,"CA")>0)+0,IF(E2=0,(COUNTIFS(A:A,A2,C:C,"E")>0)+0,0))

If your table doesn't begin at A1 and you'd like to provide the table name or the start location, I'd be happy to adapt the formula to your specific situation.

 

[FDibbins]

Eric, what is the +0 for after the countifs?

 

[FDibbins]

the3 way I understand this, you can do it all with 1 helper column...
=(B2=1)+(C2="e")+(C2="ca")+AND(C2="E",COUNTIFS($A$2:$A$5,A$2,$C$2:$C$5,"CA")>0)

This breaks down to...
D2=--(B2=1)
E2=--(C2="e")
F2=--(C2="ca")
G2=--AND(C2="E",COUNTIFS($A$2:$A$5,A$2,$C$2:$C$5,"CA")>0)

Not sure how you get 1 in G3 (Combine, row 2)?

 

[inactiveuserps07]

Ford, the +0 is one way to turn a "TRUE" or "FALSE" into its corresponding number, 1 or 0. Other ways include adding *1 or prefacing the condition with --.

So ...

COUNTIFS(A:A,A2,C:C,"CA")>0)+0

... means "Find the number of instances where Column A contains the Work Ref from this row and Column C is 'CA.' Tell me whether it's TRUE or FALSE that this number of instances is more than 0. Then convert that logical word to a number, either 1 or 0, and put that in the cell.

 

[FDibbins]

Ford, the +0 is one way to turn a "TRUE" or "FALSE" into its corresponding number, 1 or 0. Other ways include adding *1 or prefacing the condition with --.

Yes, I know what it does, but don't see the need for it? COUNTIFS will return a number 0 or greater, so "converting to a number" seems redundant?

 

[FDibbins]

Ya know, it would help if I actually read the whole formula...
,(COUNTIFS(A:A,A2,C:C,"CA")>0)+0,
apologies lol

another way...
--(COUNTIFS(A:A,A2,C:C,"CA")>0)

 

[inactiveuserps07]

Hi, Frank. Yes, +0, *1 and -- all achieve the same thing.

 

[kramart]

Hi Erik,

Many thanks for your code. It is almost what I need. This is an example where three works have the same WORKREF but there is no E.

WORKREF	STAT	TYPE	IF STATE =1	IF TYPE = E	IF TYPE = CA	COMBINE	TOTAL
24188	1	CA	1	0	1	0	2
24188	1	CA	1	0	1	0	2
24188	1	CA	1	0	1
445566	0	CA	0	0	1

<tbody>
</tbody>

There are some cases where there are few works with the same WORKREF number but they all are CA. I would like to take this into account and if there is no E in the column TYPE that would have the same WORKREF number as CA's then the formula should return 0.

Just to give you additional information.

This is just a simplified version of my table. There are more columns with diferent references and the outcomes in coulumns

IF STATE =1,

IF TYPE = E,

IF TYPE = CA

<tbody>
</tbody>

are depending on other tables. I am using formulas vlookup, search etc. Below there is an example where outcomes in the last two columns are 0 because certain conditions wasn't met.

6965	100	100	1	CA	1	0	0
6965	100	100	1	CA	1	0	0
6965	100	100	1	CA	1	0	0
6965	100	100	1	CA	1	0	0
6965	100	100	1	E	1	0	0
6965	100	100	1	SE	1	0	0

<tbody>
</tbody>

<tbody>
</tbody>

Hop that makes sense.

Thanks in advance,

Art

 

[kramart]

Hi Ford,

Many thanks for your input but to be hones I don't understand your code ... could you take me trough it please ?

the3 way I understand this, you can do it all with 1 helper column...
=(B2=1)+(C2="e")+(C2="ca")+AND(C2="E",COUNTIFS($A$2:$A$5,A$2,$C$2:$C$5,"CA")>0)

This breaks down to...
D2=--(B2=1)
E2=--(C2="e")
F2=--(C2="ca")
G2=--AND(C2="E",COUNTIFS($A$2:$A$5,A$2,$C$2:$C$5,"CA")>0)

Cheers,

Art