Solved

# Crystal - Count if with multiple qualifiers

Posted on 2008-11-07
Medium Priority
2,565 Views
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})
0
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.
0

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
0

LVL 77

Expert Comment

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

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.

James
0

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
1
else
0

Then simply add it up with a summary function.

mlmcc
0

## Featured Post

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
Course of the Month15 days, 11 hours left to enroll