3 Replies Latest reply on Aug 19, 2015 9:50 AM by Michael Dear

# Trying to replicate Excel Countif statements and having a hard time.

Hi all,

I'm having a hard time trying to replicate multiple Excel COUNTIF statements in Tableau.

I need to track a trend of open work orders week on week and have an excel file to do this.This is made up of COUNTIF formulas that sum work orders open on a particular date. To go back in time and see how many work orders were open on a particular date the formula calculates work orders open on a particular date and closed greater than that same date plus work orders open on a particular date and not closed.

I know this is possible in Tableau but at my level I can't figure out the calculation. The excel file explains my problem and formulas in more detail.

Appreciate any suggestions that you guys have.

• ###### 1. Re: Trying to replicate Excel Countif statements and having a hard time.

Hi Michael!

For finding the number of active items in a time period we can duplicate the data source and then blend on Created Date = Closed Date.  We can then use the Number of Records generated field to represent our different items, Created from the first data source and Closed from the second data source.

The next step is to find the Delta which is calculated by taking the Number of Records in the Created data source and subtracting Number of Records from the Closed data source.

Lastly we can wrap the Delta calc in a RUNNING_SUM() function to get the total number of active items over time.

I attached a sample workbook using the data source you provided, let me know how it looks

Thanks Michael!

1 of 1 people found this helpful
• ###### 2. Re: Trying to replicate Excel Countif statements and having a hard time.

I haven't viewed your Excel file, but to replicate a COUNTIF in Tableau:

COUNT[Or Any other Aggregation, like SUM] ( IF [SomeDimension]=="Some Value" then [Customer ID] END)

This will Count Customers where a Dimension is equal to some value.

• ###### 3. Re: Trying to replicate Excel Countif statements and having a hard time.

Dan,

This is exactly what I was looking for. I really appreciate your help. It just didn't occur to me that you could even do this kind of thing in Tableau. I guess I need to do some data blending training!

Once again, much appreciated for pointing me in the right direction.

Regards,

Michael