Sumif function

  • Thread starter Thread starter nc
  • Start date Start date
N

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