Sum If

[Guest]

Hi,

This is working:

=Abs(Sum(([Billed]="Yes")*[BudgetHrs]))

But, when Billed is "No", I don't want to see anything. With the above I
see a 0. How can I fix that?

TIA

 

[Guest]

It may be working but nevertheless its not good programming practice to rely
on the implementation of Boolean values as -1 or 0; its what the head of one
software company of my acquaintance termed as "being unduly chummy with the
implementation". Instead call the IIf function to return 1 or a Null:

=Sum(IIf([Billed]="Yes",1,Null)*[BudgetHrs])

If all rows have 'No' in the Billed column then the expression will evaluate
to Null.

Ken Sheridan
Stafford, England

 

[Guest]

Awesome! Thank you. I didn't like the way I was doing it either, but I
couldn't seem to get to where you got me. Thank you - works great.

It may be working but nevertheless its not good programming practice to rely
on the implementation of Boolean values as -1 or 0; its what the head of one
software company of my acquaintance termed as "being unduly chummy with the
implementation". Instead call the IIf function to return 1 or a Null:

=Sum(IIf([Billed]="Yes",1,Null)*[BudgetHrs])

If all rows have 'No' in the Billed column then the expression will evaluate
to Null.

Ken Sheridan
Stafford, England

Hi,

This is working:

=Abs(Sum(([Billed]="Yes")*[BudgetHrs]))

But, when Billed is "No", I don't want to see anything. With the above I
see a 0. How can I fix that?

TIA