sum up columnA depending on conditions on columnB and columnC

  • Thread starter Thread starter pooposa
  • Start date Start date
P

pooposa

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.
 
there may be a more elegant way but id do this
make another column with your codes which sre shown (or not) dependent on
the depreciation.Then sumif using that new column
 
Enter the equipment type you wish to total in say, D1,
And try this:

=SUMPRODUCT((B1:B5=D1)*(C1:C5>0)*A1:A5)
 
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
 
Back
Top