search for text then add
 

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).

search for text then add
 

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)

--
Biff
Microsoft Excel MVP


"r2rcode" wrote in message
...
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).

search for text then add
 

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.

"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"r2rcode" wrote in message
...
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).


.

search for text then add
 

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)

--
Biff
Microsoft Excel MVP


"r2rcode" wrote in message
...
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.

"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"r2rcode" wrote in message
...
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).


.