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

Squirl28

New Member
Joined
Sep 22, 2016
Messages
27
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
....
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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.
 
Upvote 0
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
BCDEAM
212345C1No Alert0
312345C2Alert1
412345C3Alert0
512345C4No Alert0
612345C5No Alert0
712345C6No Alert0
Sheet1


In AM2 enter and copy down:

=(COUNTIFS($B$2:B2,B2,$E$2:E2,"Alert")=1)+0
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

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