Condition Formula

kramart · Jun 6, 2017

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.

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]WORKREF[/TD]
[TD]STAT[/TD]
[TD]TYPE[/TD]
[TD]IF STATE =1[/TD]
[TD]IF TYPE = E[/TD]
[TD]IF TYPE = CA[/TD]
[TD]COMBINE[/TD]
[TD]TOTAL[/TD]
[/TR]
[TR]
[TD]24188686[/TD]
[TD]1[/TD]
[TD]E[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]24188686[/TD]
[TD]1[/TD]
[TD]CA[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]24188686[/TD]
[TD]1[/TD]
[TD]CA[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]445566[/TD]
[TD]0[/TD]
[TD]CA[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hope that make sense.

Any suggestions would be much appreciated!!!

Thanks,

A

inactiveuserps07 · Jun 7, 2017

Hi Art,

It would help to see an actual image of your sheet or table. The formula I provided worked for what was described in your first post. However, it seems you have more conditions than mentioned at first.

I'm afraid I don't understand what I'm looking at in the last data set you gave (starting with "6965 100 100 ..."). There are no column headers and I can't makes sense of the eight columns against your originally posted eight columns. Just for instance, there is the addition of "SE" — which is neither type from the original headers; and you have a 0 in the column beside the CA listings where IF TYPE=CA would be, which isn't logical (seeing that 0 means FALSE). Again, an actual image or copy-paste of the data on your sheet would be helpful.

Thanks,
Erik

inactiveuserps07 · Jun 7, 2017

Also, I originally instructed you to enter my formula in F2. This was stated incorrectly: it should have been entered into G2.

When I use this formula in Row G against your new criteria where there are several entries of CA for the same WORKREF but no entries of E for the WORKREF), I get the expected 0 in COMBINE.

inactiveuserps07 · Jun 7, 2017

This is what I get with the part of your data I understood:

[TABLE="width: 741"]
<colgroup><col><col><col><col span="5"></colgroup><tbody>[TR]
[TD]WORKREF[/TD]
[TD]STAT[/TD]
[TD]TYPE[/TD]
[TD]IF STATE =1[/TD]
[TD]IF TYPE = E[/TD]
[TD]IF TYPE = CA[/TD]
[TD]COMBINE[/TD]
[TD]TOTAL[/TD]
[/TR]
[TR]
[TD]24188686[/TD]
[TD]1[/TD]
[TD]E[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]24188686[/TD]
[TD]1[/TD]
[TD]CA[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]24188686[/TD]
[TD]1[/TD]
[TD]CA[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]24188[/TD]
[TD]1[/TD]
[TD]CA[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]24188[/TD]
[TD]1[/TD]
[TD]CA[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]24188[/TD]
[TD]1[/TD]
[TD]CA[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]445566[/TD]
[TD]0[/TD]
[TD]CA[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

Is this not what you expect?

kramart · Jun 7, 2017

Hi Erik,

I do apologise for the confusion with my last post. Been a bit busy doing other things therefore didn't realise there were some [FONT=&quot]discrepancies.[/FONT]

Your last post is exactly what i mean for this exercise. As I said before my original data is more complex and the table is bigger but for this purpose the simplified table ( your example ) is enough.

I was able to modify your formula so it works to my expectations.

Here it is:

=IF(AND(AN48>=0,(SUM(COUNTIFS(A:A,A48,E:E,{"CA","C","A"}))>0)+0,IF(AM48>=0,(COUNTIFS(A:A,A48,E:E,"E")>0)+0)),1,0)

As you can see in my original table I have two other types "C" and "A" that I need to take into equation and the columns are different.

Here is the formula that should work with the example table

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

...

what do you think about my approach ?

inactiveuserps07 · Jun 7, 2017

As far as approach, I'm a believer that "If it ain't broke, don't fix it" (i.e., if it's working, then I'm in favor of the approach). Unless it's causing significant lag, I'm not one to work extra hard to come up with the absolute shortest formula.

I'm glad you were able to modify to your needs. That was the goal, and it seems it's been achieved. Celebrate!

FDibbins · Jun 9, 2017

Sure, sorry for the delay, was not online for a while.

=(B2=1) will give either a TRUE or FALSE return, which, when you add it to anything, will be converted to either 1 or 0
So if B2=1 and C2=e or C2=ca (C2 cant be both, so it will either be 1 or 0), the answer will be 2
Then the next part...
G2=--AND(C2="E",COUNTIFS($A$2:$A$5,A$2,$C$2:$C$5,"CA")>0)
it counts for C2 = E and for the amount of times the range $A$2:$A$5 contains A$2 when the range $C$2:$C$5 also contains CA

Condition Formula

kramart

New Member

inactiveuserps07

Banned user

Excel Facts

inactiveuserps07

Banned user

inactiveuserps07

Banned user

kramart

New Member

inactiveuserps07

Banned user

FDibbins

Well-known Member

Similar threads

Forum statistics

Share this page

Condition Formula

kramart

New Member

inactiveuserps07

Banned user

Excel Facts

inactiveuserps07

Banned user

inactiveuserps07

Banned user

kramart

New Member

inactiveuserps07

Banned user

FDibbins

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock