------ Register to get rid of these "In Post" ads! ------


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

------ Register to get rid of these "In Post" ads! ------


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" <r2rcode@discussions.microsoft.com> wrote in message
news:79108BAB-238C-42E4-9B89-0F1B2F53FF4C@microsoft.com...
> 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).

------ Register to get rid of these "In Post" ads! ------


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" <r2rcode@discussions.microsoft.com> wrote in message
> news:79108BAB-238C-42E4-9B89-0F1B2F53FF4C@microsoft.com...
> > 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).
>
>
> .
>

------ Register to get rid of these "In Post" ads! ------


>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" <r2rcode@discussions.microsoft.com> wrote in message
news:5A701F05-1650-416F-B897-EE0C15C85D08@microsoft.com...
> 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" <r2rcode@discussions.microsoft.com> wrote in message
>> news:79108BAB-238C-42E4-9B89-0F1B2F53FF4C@microsoft.com...
>> > 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).
>>
>>
>> .
>>