Print Page | Close Window

How to Count fields that are non-zero (CountIf?)

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=8195
Printed Date: 05 May 2024 at 12:27pm


Topic: How to Count fields that are non-zero (CountIf?)
Posted By: ekotek
Subject: How to Count fields that are non-zero (CountIf?)
Date Posted: 02 Nov 2009 at 8:58am
Hey everyone,
Total newbie with Crystal Reports Formulas...
Hoping to get help with this..
 
Basically, I have a formula that looks like this:
 
Average ({tblEmployee_Team_Available_Hours.TotalProjectHours}) - Sum ({tblProject_Employee_TimeEstimates.Hours})
 / Count ({tblProject_Employee_TimeEstimates.Hours})
 
The only problem is that some of the records in {tblProject_Employee_TimeEstimates.Hours} are zeroes...I'd like to exclude those records...couldn't find anything similar to Excel's CountIF....
Please help....


Replies:
Posted By: Countrystyle
Date Posted: 02 Nov 2009 at 1:20pm
Not sure if this is exactly what you are looking for but ...
 
Create a formula:
if  {tblProject_Employee_TimeEstimates.Hours} = 0 then "error" else
Average ({tblEmployee_Team_Available_Hours.TotalProjectHours}) - Sum ({tblProject_Employee_TimeEstimates.Hours})
 / Count ({tblProject_Employee_TimeEstimates.Hours})
 
and then use the 'Select Expert' to exclude all 'Error's.

Posted By: DBlank
Date Posted: 02 Nov 2009 at 2:51pm
There are 3 basic ways to do this if you do not want to exlude data rows.
1.variable formula to coniditionally count (lots of examples on this site)
or
2. a Running Total (RT) to coniditionally count
RT Name: NonZeroCount
Field to summarize={tblProject_Employee_TimeEstimates.Hours}
Type of Summary=Count
Evaluate=Use a formula...
{tblProject_Employee_TimeEstimates.Hours} <> 0
reset=Never
 
(this must be used in the report footer since it is evaluating all rows).
 
3. if you have no duplicate records in the data set just use a formula to make a row =1 or 0 and then Sum that formula field
Name=RowCount
if {tblProject_Employee_TimeEstimates.Hours} = 0 then 0 else 1
SUM( mailto:%7b@rowCount - {@rowCount })


Print Page | Close Window