Sumif function

[nc]

A B C D E
Dept Text Amount Employee no. Total
JAK 3001 Fee 481.16 042670
LAN 3001 Fee 70.04 044101
JAK 3001 Fee 36.61 044101
LAN 3001 Fee 136.00 007001
AFR 3001 Fee 57.42 011800
LAN 3001 Fee 340.00 011800

I would like to use the sumif function in column E to total all the amount
according to the employee no., but exclude the amount for Dept=â€LANâ€. i.e.
the sumif function will return the total amount of £106.65 for employee no.
044101

 

[Jim Thomlinson]

In your example of emplyee of emplyee you have the record:
LAN 3001 Fee 70.04 044101
should that record be excluded? You asked to exclude LAN but your example
includes it...

 

[Gilbert]

I would use a pivot table for this but if you still want to use the sumif I
would first create a helper column concantenating the employee and dept.
After you do that, you can then apply the formula to the helper column.

 

[Jim Thomlinson]

Here is one way assuming you actually want to exclude LAN

=sumproduct(--($A$2:$A$10<> "LAN"), --($D$2:$D$10= $D2), $C$2:$C$10)
Entered in cell E2

 

[nc]

Thanks.

What about if I wanted include both dept "LAN" and "JAK"?