5 Replies Latest reply on Jun 18, 2018 3:22 PM by Deepak Rai

# Count one column based on another column Logical test

Hi,

I am trying to create a formula that states:

CountIf(PHq9Date) > Dateadd('day',-90, (Con60_Date) and PHQ9Date <  Con60_Date)

I have tried doing the following, creating 2 separate functions:

Step 1

If [Phq9 Date]<= [CON60_Date] and [Phq9 Date] > Dateadd('day',-90, [CON60_Date]) Then 1

Else 0

END

Step 2

Sum([Step 1])

I also combined them at first - but that didn't work either.  What is happening is it seems to  be adding up the date figures and summing them together.  They are both Date Fields and if I test Step 1 I do get 1's where I'm supposed to at the client level but it won't sum them up.

Please don't ask me to attach a WB, if you are going to, then don't respond it is a waste of each of our time.  I am unable to attach the book and don't have the time to clean up the data to provide an anonymous version.  If that means you don't want to or are unable to help then that is fine.

Thank you,

Scott

• ###### 1. Re: Count one column based on another column Logical test

If   [Phq9 Date] > Dateadd('day',-90, [CON60_Date]) AND [Phq9 Date]<= [CON60_Date]Then 1

Else 0

END

So you can Drag it to Row or Label to aggregate as Sum

or Use This;

SUM(

If   [Phq9 Date] > Dateadd('day',-90, [CON60_Date]) AND [Phq9 Date]<= [CON60_Date]Then 1

Else 0

END)

1 of 1 people found this helpful
• ###### 2. Re: Count one column based on another column Logical test

Thank you Deepak,

That worked great, must have been my order.

• ###### 3. Re: Count one column based on another column Logical test

Great!!!