Sum one field based on value in another field

[ksnielsen]

Hello-
I am using Crystal Reports XI. I need to total the deposits for an invoice when field "Postage Deposit" is True. I was trying to use the Sum(fld, condFld, cond) function but it errors.

This is what I entered:
Sum ({CashRecDetail.Amount-Deposit-Applied}, {CashRecDetail.Postage-Deposit}, True)

I get the error "A group condition must be a string" and True is highlighted.

I also tried to make the condFld a group. this errors too.
Sum ({CashRecDetail.Amount-Deposit-Applied},GroupName ({CashRecDetail.Postage-Deposit}), True)

Can someone steer me in the right direction?

Thanks, Karen

 

[MCuthill]

ksnielsen,

I would suggest the follwing formula field, which can then be Summarized as warranted. I have presumed that {CashRecDetail.Postage-Deposit} contains the string to be evaluated, and that {CashRecDetail.Amount-Deposit-Applied} has the amount.

{@SUM_PostageDeposits}

[blue]IF[/blue] {CashRecDetail.Postage-Deposit} = [blue]True THEN[/blue] {CashRecDetail.Amount-Deposit-Applied} [blue]ELSE[/blue] 0

**This formula will populate the value of {CashRecDetail.Amount-Deposit-Applied} for those records where {CashRecDetail.Postage-Deposit} is True, and a zero when it is not. By summing this field, it is effectively the same as a SUMIF() in MS Excel.

Please note: if {CashRecDetail.Postage-Deposit} is a STRING, and not a BOOLEAN; please change the above formula to the following:
{@SUM_PostageDeposits}

[blue]IF UpperCase[/blue]({CashRecDetail.Postage-Deposit}) = "TRUE" [blue]THEN[/blue] {CashRecDetail.Amount-Deposit-Applied} [blue]ELSE[/blue] 0

Hope this helps! Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[jcrawford08]

Try:
Sum ({CashRecDetail.Amount-Deposit-Applied}, {CashRecDetail.Postage-Deposit}, 'True')

It's looking for the actual string that the boolean returns, and so the True needs to be in quotes...


-jcrawford-

Not in word only, but in deed also... 1Jn3:18

 

[MCuthill]

jcrawford,

Sure wish I had thought of that... I hadn't seen the 3rd argument on the SUM() function before. I wish I had put 2 and 2 together when I read the post and thought of the Field Type being the issue, not that SUM() couldn't have 3 arguments!

Much more concise than my traditional approach.

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[jcrawford08]

A very valuable evaluation tool indeed! I'm glad I could help show you another tool to place in your tool box MCuthill

-jcrawford-

Not in word only, but in deed also... 1Jn3:18

 

[ksnielsen]

Thank you for the quick responses. I had to use MCuthill's method. When I tried jcrawford08's method I got a different error.

I think I need to have this in a subreport so I am trying to figure that out now. If I like the tables in the main report the way I need them to get the correct postage deposit amount, it screws up other things.

Trying to read about subreports to see if I can figure it out.

Thanks again.
Karen

 

[lbass]

The third argument is only for date groups to indicate whether the group is weekly, monthly, etc., e.g.,

sum({table.amt},{table.date},"weekly")

-LB