Summing with Criteria

[Pixie78]

I thought I found this before, but I sure can't find it again.
My report lists all the employees by name, (group header), and lists all the
classes they have taken. The classes have a few fields of information and 2
that I need to calculate are 'Status' and 'CLength'.
Since an employee may have taken many different classes, the length of the
classes will vary. I need to say:

Sum all the lengths of the classes for this employee where their status =
Completed
I have a text box already that totals the # of 'Completed' classes for each
employee in the group footer, if that helps.

I also have a text box that totals all other Status' named 'Total Missed'.
I also need to say:
Sum all the lengths of the classes for this employee where their status does
not = Completed........... or = 'Absent', 'Vacation', 'Medical'..... which
ever is easier

Thanks so much.

 

[Marshall Barton]

I thought I found this before, but I sure can't find it again.
My report lists all the employees by name, (group header), and lists all the
classes they have taken. The classes have a few fields of information and 2
that I need to calculate are 'Status' and 'CLength'.
Since an employee may have taken many different classes, the length of the
classes will vary. I need to say:

Sum all the lengths of the classes for this employee where their status =
Completed
I have a text box already that totals the # of 'Completed' classes for each
employee in the group footer, if that helps.

I also have a text box that totals all other Status' named 'Total Missed'.
I also need to say:
Sum all the lengths of the classes for this employee where their status does
not = Completed........... or = 'Absent', 'Vacation', 'Medical'..... which
ever is easier

=Sum(IIf(status = Completed, CLength, 0))
=Sum(IIf(status <> Completed, CLength, 0))

 

[Pixie78]

Thanks Marshall!! You are the best! Works perfectly =)