Return 1 if criteria met or 0 if not - based on two criteria with both columns having multiple instances (duplicates).

[Squirl28]

Hi,

In column B I have ticket numbers and there are 12 instances of each ticket (may grow). In column D I have clients. In column E I have specific text (e.g., alert, no alert, etc). In column AM I need it to show 1 or 0. I want it to only count the first instance of colum B (ticket number) and in column E count first instance of “Alert”. I’m working with a table with 1000 + records.

Example: This range should never show more than 1 for 12 instance range. In other words return 1 the first time you see ticket and alert on same row.

B D E AM
12345 C1 No Alert 0
12345 C2 Alert 1
12345 C3 Alert 0
12345 C4 No Alert 0
12345 C5 No Alert 0
12345 C6 No Alert 0
....

 

[Squirl28]

That worked like a charm! Thank you again

 

[Aladin Akyurek]

That worked like a charm! Thank you again

Great. Thanks for the update.

 

[Squirl28]

I’m hoping you can help me again, same sheet, but have an additional ask.

Hi,
I’m stumped and need help. I have a file with thousands of entries and I’m trying to add 3 columns that count the first instance of a word in that specific range. It will check for “notable”, “minor”, “test”.

Column B will have ticket numbers (many of them are duplicates ticket numbers but for different clients), column F will have notable, minor and test. which many of them are duplicates. I only want to count the first instance of each word based on the ticket (notable, minor and test). The below works great for doing just that. However, the problem is that in some cases one client may count it as minor where another counts it as notable. In that scenario I only want notable to be counted, as that takes priority and don’t want to count both. If they don’t both show up for that ticket number they should be counted independently like below.

=IF($F2=”Notable”,IF(Countifs($B2:B2,B2,$F$2:F2,”Notable”)=1,1,0),0)

=IF($F2=”Minor”,IF(Countifs($B2:B2,B2,$F$2:F2,”Minor”)=1,1,0),0)

=IF($F2=”Test”,IF(Countifs($B2:B2,B2,$F$2:F2,”Test”)=1,1,0),0)

4th column is a sum of those theee columns to get me a total.

https://www.dropbox.com/s/i5uhg6yn58mo16y/Copy of Notable vs minor.xlsx?dl=0

 

[Aladin Akyurek]

Your dropbox file is confusing.

In which columns must "notable", "minor", and "test" be evaluated?

 

[Squirl28]

I want to create 4 columns 5 if necessary. I want individual counts of
“notable” column G
“minor” Column H
“test” Column I
Column J would have sum of G:I

I believe the formula would go in the “minor” column to say only count first instance of minor based on that ticket if that same ticket doesn’t have “notable”.

I’m okay with other solutions to get this. Just need to have “notable” “minor” and “test” called out separately and “notable” trumps all. If it says “notable” count that first and only. If not “notable” count it based on “minor” or “test”. Test will never show up on the same ticket as minor and notable.

Let me know if this is still confusing

 

[jtakw]

Hi,

Question also posted here: https://www.mrexcel.com/forum/excel...terias-met-if-not-return-0-a.html#post5108170

 

[Squirl28]

I believe I found an easier way to explain what I’m looking for (hopefully).

When counting for “minor”

In essence for the first instance of ticket number look at column F see if it equals minor. If so, go back and look at that same ticket number and see if there is an instance of notable. If so, return 0 for minor. If there is no instance of notable just return 1 as normal.

Below is what I used, but in some situations it will count both notable and minor giving me an extra count for same ticket. The sum should never be more than 1 per ticket number. Notable always takes priority and then minor.

=IF($F2=”Notable”,IF(Countifs($B2:B2,B2,$F$2:F2,”Notable”)=1,1,0),0)

=IF($F2=”Minor”,IF(Countifs($B2:B2,B2,$F$2:F2,”Minor”)=1,1,0),0)

=IF($F2=”Test”,IF(Countifs($B2:B2,B2,$F$2:F2,”Test”)=1,1,0)

 

[Aladin Akyurek]

Perhaps...

G2:

=IF($F2="Notable",IF(COUNTIFS($B$2:B2,B2,$F$2:F2,"Notable")=1,1,0),0)

H2:

=IF($F2="Minor",IF(COUNTIFS($B$2:B2,B2,$F$2:F2,"Minor")=1,1-(COUNTIFS($B$2:$B$12,B2,$F$2:$F$12,"Notable")>0),0),0)

I2:

=IF($F2="Test",IF(COUNTIFS($B$2:B2,B2,$F$2:F2,"Test")=1,1-(COUNTIFS($B$2:$B$12,B2,$F$2:$F$12,"Notable")>0),0),0)

 

[Squirl28]

You’re a life saver, this worked perfectly !!! Thank you once again.

 

[Aladin Akyurek]

You’re a life saver, this worked perfectly !!! Thank you once again.

You are welcome. Thanks for providing feedback.