| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum up columnA depending on conditions on columnB and columnC
Please help me on the following problem. column A is equipment purchase price column B is type of equipment, such 'c' for computer, 's' for software, 'f' for furniture column C is the monthly depreciation value question: to sum up each equipment purchase prices if the monthly depreciation value is not zero. Example: columnA Column B Column C $5000 c $0 $3000 c $50 $6000 f $100 $6000 c $100 $9000 f $0 I need to sum up computer purchased for this month which should be $3000+6000 = 9000 and furniture purchased for this month is $6000. Thanks. -- pooposa ------------------------------------------------------------------------ pooposa's Profile: http://www.excelforum.com/member.php...o&userid=37122 View this thread: http://www.excelforum.com/showthread...hreadid=568548 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum up columnA depending on conditions on columnB and columnC
Enter the equipment type you wish to total in say, D1,
And try this: =SUMPRODUCT((B1:B5=D1)*(C1:C50)*A1:A5) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "pooposa" wrote in message ... Please help me on the following problem. column A is equipment purchase price column B is type of equipment, such 'c' for computer, 's' for software, 'f' for furniture column C is the monthly depreciation value question: to sum up each equipment purchase prices if the monthly depreciation value is not zero. Example: columnA Column B Column C $5000 c $0 $3000 c $50 $6000 f $100 $6000 c $100 $9000 f $0 I need to sum up computer purchased for this month which should be $3000+6000 = 9000 and furniture purchased for this month is $6000. Thanks. -- pooposa ------------------------------------------------------------------------ pooposa's Profile: http://www.excelforum.com/member.php...o&userid=37122 View this thread: http://www.excelforum.com/showthread...hreadid=568548 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum up columnA depending on conditions on columnB and columnC
Try this: With data as above in A1:C5, in A7 enter: =SUMPRODUCT(($B$1:$B$5="c")*($C$1:$C$5<0)*(A1:A5) ) and in A8: =SUMPRODUCT(($B$1:$B$5="f")*($C$1:$C$5<0)*(A2:A6) ) Regards Mike -- Mikeopolo ------------------------------------------------------------------------ Mikeopolo's Profile: http://www.excelforum.com/member.php...o&userid=18570 View this thread: http://www.excelforum.com/showthread...hreadid=568548 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum up columnA depending on conditions on columnB and columnC
=SUMPRODUCT(--(B1:B5="c"),--(C1:C5<0),--(A1:A5))
Regards, Alan. "pooposa" wrote in message ... Please help me on the following problem. column A is equipment purchase price column B is type of equipment, such 'c' for computer, 's' for software, 'f' for furniture column C is the monthly depreciation value question: to sum up each equipment purchase prices if the monthly depreciation value is not zero. Example: columnA Column B Column C $5000 c $0 $3000 c $50 $6000 f $100 $6000 c $100 $9000 f $0 I need to sum up computer purchased for this month which should be $3000+6000 = 9000 and furniture purchased for this month is $6000. Thanks. -- pooposa ------------------------------------------------------------------------ pooposa's Profile: http://www.excelforum.com/member.php...o&userid=37122 View this thread: http://www.excelforum.com/showthread...hreadid=568548 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
