search for text then add

  • Thread starter Thread starter r2rcode
  • Start date Start date
R

r2rcode

Cat. No. Cost per
M 20 $2.00
W 10 $1.00
M/W 5 $1.00
M 20 $2.00

based on the matrix above i need a formula to compute the following:
1) search down column 1 for a "M" in the cell/word then total the quantity
in the corresponding row. (for this example "M" would add up to 45, "W"
would be 15).

2) then i need it to be able to total the cost spent for each category (for
this example "M" would be 20*$2+5*$1+20*$2).
 
Try this...

=SUMIF(A2:A5,"*M*",B2:B5)

Better to use a cell to hold the criteria...

E2 = M

=SUMIF(A2:A5,"*"&E2&"*",B2:B5)
 
thanks. and what formula will do part 2) of my question where it multiplies
the qty by the cost per and then sums up all the products for a certain
category.
 
thanks. and what formula will do part 2)

Ooops! I don't know how I missed that part.

Try this...

E2 = M

=SUMPRODUCT(--(ISNUMBER(SEARCH(E2,A2:A5))),B2:B5,C2:C5)
 
Back
Top