Replicate IF(COUNTIFS) Excel Formula

[juannc11]

Good afternoon,

I'm having trouble replicating the following Excel formula my workflow:

=IF(COUNTIFS($A$2:A2,A2,$G$2:G2,G2)>1,0,1)

Column A is an employee ID and column G is a team ID. Since there are duplicate Employee IDs I use the formula to count the first instance that an Employee ID pops up given that the Emp ID and Team ID are the same. I am feeding this data to a Pivot Table. The formula allows me to set the Pivot Table to count for the first instance of the ID while keeping the duplicate IDs when drilling down into the data. I'm keeping the duplicate ID's because the rows show different sub categories that an employee may be supporting within one team. 

Example:

EMP ID	Team ID	UniqueValCount (IF(COUNTIFS) Formula)
251a	aa	1
251a	aa	0
251a	aa	0
251a	bb	1
252a	cc	1
253a	cc	1

Using the Summarize Tool doesn't allow me to assign 0's to the duplicate values after the first instance.

Any help is much appreciated! 

[kelsey_kincaid · Accepted solution]

Hi @juannc11 ,

This sounds like a good use case for the Multi-Row Formula tool. Give this a try and let me know if it works for your use case!

[Qiu · Accepted solution]

@juannc11 

Maybe something like this would help.

[Qiu]

@juannc11 
If you find my workflow useful, appreciate you would mark it as accept as well.

[Qiu]

@juannc11 
Thank you so much😁