Crystal - Count if with multiple qualifiers

Posted on 2008-11-07
Medium Priority
Last Modified: 2012-05-05
I want to count Column A if Columns B and C meet a certain criteria:

Col A     Col  B    Col C
1            True     Original
2            True     Original
3            null       Original
4            True     Canceled

The result I am looking for would be a sum of 2 from the above data.

I think the formula I came up with is close but I keep getting a "The Keyword 'Then' is missing" Here is what I came up:

if ({tblCases.IsComplete} = true) and
if ({tblCases.CaseStatus} = 'original')) then Count ({tblCases.NumberCases})
Question by:dsabine
LVL 77

Expert Comment

ID: 22905486
We need to be quite clear about what you want here.

Count() counts 1 for each item, irrespective of the item's value.
If you want to add up the values then you need Sum not Count.

But it looks like you should be using a Running Totals field for this whichever you want.

Seelect the field to summatrise as ColA.
Select Sum or Count whichever it is you want.
In the 'Evaluate when' you select Use formula and in the formul you put:

({tblCases.IsComplete} = true) and ({tblCases.CaseStatus} = 'original'))

If you want one total for the whole report you set Reset to Never.
Put the field in your report footer.

Author Comment

ID: 22905574
OK.....then to be clear, I need to SUM on the total number of cases not a count. I need to:

Count tblCases.NumberCases
if tblCases.CaseStatus = 'original and tblCases.IsComplete
LVL 77

Expert Comment

ID: 22905678
As I said , use a running totals field as I described.
LVL 35

Expert Comment

ID: 22912180
I think Peter's got you pretty well covered to get your count.  If you're trying to get a count of the records where certain conditions are true, then, as he said, you can use a running total to do a count, but only when your conditions are met.

 FWIW, the error from your original formula was because of the "and if" between your two conditions.  That should have just been "and", without the "if".

 if a and b then c      // Valid
 if a and if b then c   // Not valid

 Just to be clear, the formula still wouldn't have given you the count you wanted.  Count () gives you a final count every time you use it, as opposed to adding 1 to a count every time you use it, which seemed to be what you were expecting.

LVL 101

Accepted Solution

mlmcc earned 2000 total points
ID: 22919628
That should work.

You could also use a formula like

if ({tblCases.IsComplete} = true) and  ({tblCases.CaseStatus} = 'original')) then

Then simply add it up with a summary function.


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question