Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Count if Function in crystal? Post Reply Post New Topic
Page  of 2 Next >>
Author Message
althomas
Newbie
Newbie


Joined: 09 Nov 2010
Online Status: Offline
Posts: 36
Quote althomas Replybullet Topic: Count if Function in crystal?
    Posted: 18 Jan 2011 at 5:05am
Hi,
 i was trying to create a report in which i want to count group totals only if they are greater than 0. Does anyone know how to write this formula.
 
I am trying to create a payment edit list in which the report pulls in all the invoices that are due for payment. Among these invoices, there are some credit memos which have negative balances and no checks will be be written. They are grouped by vendor totals (A vendor could have multiple invoices). I want to count the group total only if its greater than 0, in other words how many checks to write. since i am not going to write checks for credit memos (negative amounts), i dont want to include them in my could.
 
In excel i would've write is as : Countif (Vendor Group Total) > 0
 
Any help is greatly appreciated......
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 18 Jan 2011 at 5:29am
Running Total
field to summarize = vendor
type=DistinctCount
evaluate=use a formula...use your criteria here...like...SUM(sredit,vendor)>0
reset=never
place in report footer
IP IP Logged
althomas
Newbie
Newbie


Joined: 09 Nov 2010
Online Status: Offline
Posts: 36
Quote althomas Replybullet Posted: 20 Jan 2011 at 9:24am
I am a noob so, didnt quite understand what you wrote. i try to use 2 formulas
Checks to write:   If the Group total > 0 then 1 else 0
Summary : Sum (checks to write)
 
My problem is that it's not letting me sumarize the "checks to write" formula.
 
"Count" = if Sum ({APInvHed.InvoiceAmt}, {APInvHed.Calc_SupplierName})>0 then 1 else 0
 
The above formula just takes the goup total for each supplier and if that total is greater than 0 then 1      : Formula below was trying to sum this "count" formula of all the 1's.
 
"Sum": = sum{@count}
 
When i try to compile "Sum" i get the message : "The remaining text does not appear to be part of the formula"
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 20 Jan 2011 at 9:47am
In the Field Explorer look ofr the Running Totals Fields section
Right click on it and select New
RT name = whatever
field to summarize = APInvHed.Calc_SupplierName
type of summary = DistinctCount
evaluate= select use a formula...click on the formula box... enter your criteria here...
Sum ({APInvHed.InvoiceAmt}, {APInvHed.Calc_SupplierName})>0
reset=never
place in report footer
IP IP Logged
althomas
Newbie
Newbie


Joined: 09 Nov 2010
Online Status: Offline
Posts: 36
Quote althomas Replybullet Posted: 20 Jan 2011 at 10:49am
Thank You so much!!!!!! i cant believe it worked..... Thanks again..
IP IP Logged
althomas
Newbie
Newbie


Joined: 09 Nov 2010
Online Status: Offline
Posts: 36
Quote althomas Replybullet Posted: 15 Feb 2011 at 5:52am
Hi,
 Would it be possible to help me with another question?
 
I wanted to conditional sum a group total.  In excel i would use the sumif function. I cant seem to find the sumif function in crystal.
 
What i want is the following:
 
Sum the Group Total if the Group Footer name is "x"
 
 
Thanks in advance for your help. Again i am a noobi so, please give me details.  thanks alot.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 15 Feb 2011 at 5:56am

can't really do that per se.

how are you determining the group footer "x"
IP IP Logged
althomas
Newbie
Newbie


Joined: 09 Nov 2010
Online Status: Offline
Posts: 36
Quote althomas Replybullet Posted: 15 Feb 2011 at 6:03am
I did a summary of the details and dropped it on the Group Footer ("X")  Then i dragged the column heading from my field explorer next to the sumarized footer. So now i have the Heading (dragged in from field explorer) next to Summary (of details of the heading)
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 15 Feb 2011 at 6:11am
so now to go with the count you also need a corresponding SUM ?
make a new Running Total
RT name = whatever
field to summarize = APInvHed.InvoiceAmt
type of summary = SUM
evaluate= select use a formula...click on the formula box... enter your criteria here...
Sum ({APInvHed.InvoiceAmt}, {APInvHed.Calc_SupplierName})>0
reset=never
place in report footer
 
Dis that give you the expected amount?


Edited by DBlank - 15 Feb 2011 at 6:11am
IP IP Logged
althomas
Newbie
Newbie


Joined: 09 Nov 2010
Online Status: Offline
Posts: 36
Quote althomas Replybullet Posted: 21 Feb 2011 at 9:04am
Thanks for all your help. is there a special way to sum totals from main report and subreport to give a grant total? I feel bad asking you for every time, but if it's something easy then please help me...i want to learn Crystal report
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.