I am stuck with something that I feel like should be relatively simple.

I am trying to create a count column that only counts the IDs that are only classified with DC, and only counts it for the first instance.

Expected outcome:

So far what I thought of was to create an unique ID in order to only count the first instance:

I tried the following formula but it's not correct as it cannot correctly identify only the cells that fit my criteria.

=IF(A2=A1,0,COUNTIF($A$2:$A$7,C2:C7="DC"))

Any help is much appreciated.

CodePudding user response：

Your question is unclear on several aspects, for example the first and last rows match the criteria you mentioned (classified as DC and first instance). In addition the count is showing "1", where there are 2 x "DC" instances in the ID? Would assume this is sorted in Key order. So with that...

The trick is to work out the cell in which the first instance that the criteria occurs within, and then execute the count.

=IF(AND(COUNTIF(A$2:A$7,A2)>1,COUNTIF(A$2:A2,A2)=1),COUNTIF(A$2:A$7,A2)-1,0)

This IF statement checks if the key appears more than once, AND if it is the first occurrence of the key. If these are both true, then execute the count. I have assumed you don't want to count the first instance (hence the -1), so keep or remove that if it makes sense. Please take careful note of the absolute references, particularly in the second COUNTIF which only counts down to the current row (A2).

Update.

With the comments below, if you are only looking to mark the row, then the following will show a true/false that takes into account your updated criteria. If you need more, use this as the basis for an "IF" statement.

=AND(COUNTIF(B$2:B$7,B2)=COUNTIF(A$2:A$7,A2),COUNTIF(A$2:A2,A2)=1)