Countifs not working

[smalik]

I am trying to count unique values between column H & I but my countifs is not working. I think I am not using the right formula.

I like to count the same combination from Column H & I and list the result in Column J. For example, "OSI" from Column H and "33-CE-1-10" from Column I combination should be counted only once no mater how many times the same combination appears in the data. Is there a way to do this? I don't think Countifs is the right formula to use in this case.

Any help is greatly appreciated

 

[smalik]

Just thought about writing an if statement
=IF(COUNTIFS(H:H,H16, I:I,I16)>1,1,COUNTIFS(H:H,H16, I:I,I16))

I will go with the above unless there is a better way.
Thanks.

 

[jtakw]

Hi,

This should also work.

=MIN(COUNTIFS(H:H,H16,I:I,I16),1)

So if the COUNT comes out 0, result is 0, if the COUNT is Greater than 1, result is 1.

EDIT: realized I forgot to remove the >1 from your formula when copied and pasted, corrected now.

 

[Fluff]

Not sure if I'm missing something here, but won't you just end up with 1 in every cell in col J?

 

[smalik]

Not sure if I'm missing something here, but won't you just end up with 1 in every cell in col J?

You are absolutely correct. I realized my mistake as I was reviewing the data. Here is what I am trying to do:

Formula should look at the "Sequence" (Column C) first. If there are multiple then look at "ICO" (Column B). If they are different the count should be 2 or 3 or 4 depends on how many different ICOs are for the same sequence. (See rows 2 & 3). In other words, same combination of "Sequence" and "ICO" will get a count of 1 (rows 4 & 5)

Obviously, if there is only one unique code in "Sequence" and one unique code in "ICO" the count is 1.

I am happy to add extra (helper) columns, if need be, to go through a multistep formula.

Hopefully, I am explaining my problem a little better this time.

 

[Fluff]

How about

=ROWS(UNIQUE(FILTER($B$2:$B$100,$C$2:$C$100=C2)))

 

[smalik]

How about

=ROWS(UNIQUE(FILTER($B$2:$B$100,$C$2:$C$100=C2)))

You are a life saver. Thank You.

 

[Fluff]

Glad we could help & thanks for the feedback.