Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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). |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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). |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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). . |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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). . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
search for text then return text | Excel Worksheet Functions | |||
Nested formula to search a text string and return specific text | Excel Worksheet Functions | |||
How to search for text? | Excel Discussion (Misc queries) | |||
Fun with text functions - search for text | Excel Worksheet Functions | |||
Search, find or lookup defined text in text string | Excel Worksheet Functions |