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
....

 

[Aladin Akyurek]

In D2 enter and copy down:

=(COUNTIFS($A$2:A2,A2,$C$2:C2,"Alert")=1)+0

 

[Squirl28]

Thank you for quick response. Doesn’t seem to be working. This is giving me more than 1 for the 12 instances. It is returning a 1 when line shows No Alert too. Essentially there should either be 12 0’s or 11 0’s and one 1 at most.

 

[Aladin Akyurek]

Thank you for quick response. Doesn’t seem to be working. This is giving me more than 1 for the 12 instances. It is returning a 1 when line shows No Alert too. Essentially there should either be 12 0’s or 11 0’s and one 1 at most.

It seems we have here a problem of range mapping...

Book1
	B	C	D	E	AM
2	12345		C1	No Alert	0
3	12345		C2	Alert	1
4	12345		C3	Alert	0
5	12345		C4	No Alert	0
6	12345		C5	No Alert	0
7	12345		C6	No Alert	0
Sheet1

In AM2 enter and copy down:

=(COUNTIFS($B$2:B2,B2,$E$2:E2,"Alert")=1)+0

 

[Squirl28]

I did that and still getting multiple 1’s and 1’s for “no alert”. I even opened a new sheet and pasted just values and did the formula and still getting the same thing. I am getting the green triangle in the cells below AM3 and beyond, but not in AM2 if that matters.

 

[Squirl28]

I believe I know why it is doing this, but could be wrong. It is looking back in that range looking for the word “alert” and counting it. It appeared once already, just don’t want to count it again if that makes sense.

 

[Aladin Akyurek]

I believe I know why it is doing this, but could be wrong. It is looking back in that range looking for the word “alert” and counting it. It appeared once already, just don’t want to count it again if that makes sense.

https://www.dropbox.com/s/5g5hwt3121jy1hi/Squirl28 countifs first instance.xlsx?dl=0

 

[Squirl28]

Thank you, I see the formula works. However, it is still not working when I put my data in. Or use that formula on my data. Any suggestions?

 

[Aladin Akyurek]

Thank you, I see the formula works. However, it is still not working when I put my data in. Or use that formula on my data. Any suggestions?

You might want to post a part of data where it does not succeed.

 

[Squirl28]

I could only do a screenshot, your help is greatly appreciated.
https://www.dropbox.com/s/3nuob8pnlpndxgd/image001.png?dl=0