8 Replies Latest reply on Jul 13, 2018 3:24 PM by Emily Skoraczewski

# Count If Statement

Hello, I found a formula on a discussion from about 5 years ago, and I used the formula provided, which was for, essentially a Count/Sum If.

For some reason, instead of getting a '1', I'm getting a '2' entered in my calculation:

Count Function: IF [Rapid Check] <= 2 THEN 1 ELSE 0 END

- Then I applied a Sum to the table calculation

Rapid Check Formula:

IIF([Status Changed To Hold Auth At] > [Asset Checked In At], DATEDIFF('minute',[Asset Checked In At],[Status Changed To Hold Auth At]),Null)/60

Why am I getting a 2, not a 1?  I'm so confused!

• ###### 2. Re: Count If Statement

Can you try using Average

IF Avg([Rapid Check]) <= 2 THEN 1 ELSE 0 END

• ###### 3. Re: Count If Statement

Well, if I use the CountD, I get 2, if I use Avg. I get 0.

The correct number is 4.  I've calculated the data out in Excel, and can see the number I should get is 4.  For some reason, when I sum it, it doubles it.

data attached.

• ###### 4. Re: Count If Statement

Emily, We can't open .twb as it is without data, pl attach .twx, so what is CORRECT number you are Looking For 1 or 4?

• ###### 5. Re: Count If Statement

Sorry about that, here is the corrected attachment.

The correct number is 4

Data screenshot:  I'd expect to count all four of the red lines.  These have the RC hours of <= 2

• ###### 6. Re: Count If Statement

I See this

• ###### 7. Re: Count If Statement

sorry, won't be able to post data. I'll keep playing with it to see if I can figure it out on my own

• ###### 8. Re: Count If Statement

ok, taking another stab at this. I cannot post my data, so I'm hoping the more details provided here will help.

This is what I'm seeing:

Formula: "RC <= 2 hrs":  IF [RapCheck] <= 2 THEN 1 END

RapCheck: IIF([Status Hold] > [status Checked In],DATEDIFF('minute',[status Checked In],[Status Hold]),Null)/60

Looking at another workspace I have, I can see that this is correctly, applying a '1' to my RC field.

I've added this formula in the last column: COUNTD([RC <= 2 hrs])

Now, what I want to do, is take this formula and roll it up to get the Total number of RC <= 2 by Customer.  Would this be a LOD calculation? OR how can I get the total number of RC <=2?

If I do this: COUNT([RC <= 2 hrs]) - I get 55

If I do this: COUNTD([RC <= 2 hrs]) - I get 1

If I do this: SUM([RC <= 2 hrs]) - I get 55

The number I need to get is 28! When I look at how many cases in Tableau and in Excel that have RC <=2, the number is 28.  I can see that in Tableau, I just cannot get it to total.

Any help is greatly appreciated.