| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to nest a left function within a sumif function? | Excel Worksheet Functions | |||
| Using the TODAY() function in a SUMIF function | Excel Worksheet Functions | |||
| SUMIF Function Inside SUMPRODUCT Function | Excel Worksheet Functions | |||
| Can SUMIF function include AND function | Excel Worksheet Functions | |||
| SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
Linear Mode
