Sum If

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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.

Ken Sheridan said:
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

Karin said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sum If 7
SumIf Help 5
Excel formula, sum the value cell and if empty cell left blank 0
SUM BY CRITERIA 6
Query Help, 2
Aggregate Function Error 0
summing values in each record (summing across rows) 2
Count 3

Back
Top