|
Sumif function
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 |
Sumif function
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... -- HTH... Jim Thomlinson "nc" wrote: 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 |
Sumif function
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. "nc" wrote: 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 |
Sumif function
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 -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "nc" wrote: 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 |
Sumif function
Thanks.
What about if I wanted include both dept "LAN" and "JAK"? "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "nc" wrote: 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 |
| All times are GMT +1. The time now is 02:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com