4 Replies Latest reply on Dec 8, 2016 4:52 PM by Ivan Young

# Calculation Similar to COUNTIF

I have a table that has 1 row for each employee

In that row, it lists the employee's manager's name and ID#

I am trying to calculate the number of direct reports each employee has, as shown below. For example Frank has 3 direct reports (Bob, Dan, and Elliot). So on Frank's row, the  # 3 appears in the EE Direct Span column.

Does anyone know how I would calculate this in tableau?  Is there a comparable function(s) that would be similar to how COUNTIF in excel works?

I thought it may be like this,   { FIXED [Mgr ID ] : countd([EE ID])} but this would result in #3 for Bob, Dan, and Elliot which is NOT what i am looking for it to do.

Thanks!

 EE ID EE Name Mgr ID Mgr Name EE Direct Span 1234 Adam 8910 Chris 1 4567 Bob 1516 Frank 0 8910 Chris 2122 Hans 1 1112 Dan 1516 Frank 0 1314 Elliot 1516 Frank 0 1516 Frank 1234 Adam 3

 EE ID EE Name Mgr ID Mgr Name EE Direct Span 1234 Adam 8910 Chris =COUNTIF(\$C\$4:\$C\$9,A4) 4567 Bob 1516 Frank =COUNTIF(\$C\$4:\$C\$9,A5) 8910 Chris 2122 Hans =COUNTIF(\$C\$4:\$C\$9,A6) 1112 Dan 1516 Frank =COUNTIF(\$C\$4:\$C\$9,A7) 1314 Elliot 1516 Frank =COUNTIF(\$C\$4:\$C\$9,A8) 1516 Frank 1234 Adam =COUNTIF(\$C\$4:\$C\$9,A9)
• ###### 1. Re: Calculation Similar to COUNTIF

Hi Christine,

Do let me know if this is how you want it to be

• ###### 2. Re: Calculation Similar to COUNTIF

Hi Christine,

You can do a countif by nesting an if statement in a count, however Tableau does not let you select ranges like excel, it's very different in that regard.  What you'd do for this type of analysis is to do a self join or blend.  Since I don't know your data source but guessing it's excel I've attached an example using blending.  The trick is to duplicate EE id in the primary name the field Link,  then duplicate Mgr ID in the secondary and name it Link.  This is the field you would blend on.  See the attached and let me know if you have any questions.

Regards,

Ivan

• ###### 3. Re: Calculation Similar to COUNTIF

i used a formula similar to what you shared with me:

{ FIXED [Manager ID] : countd([EE ID])}

however, for each employee row EE ID, it results in the total count # of times the Manager ID shows up in the manager direct span field.

for ex: for Bob, Dan, Elliot,  the formula results in 3

Just to clarify the request, EE Direct Span is the field, that i am trying to calculate. The sample highlighted in yellow above, shows what my expected results would be, I just don't know what formula to use.

• ###### 4. Re: Calculation Similar to COUNTIF

Hi Christine,

I'm not sure if you took a look at my post but it does return the results per your sample.

Regards,

Ivan