Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If & sumproduct formula with text
I have 3 columns A, B, C. Column A has rows with either a blank cell, the
word "shift" or the word "hazard". Columns B & C have numbers. I want the formula to look at a range in column A (A1:A20), and if a row has the word shift, to multiply columns B & C for the same range. I tried the following formula with no luck =IF(A1:A20="shift",SUMPRODUCT(B1:B20,C1:C20),0) Obviously, not the correct formula for this situation. Any help would be greatly apprectied! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If & sumproduct formula with text
Hi Jana
Not sure if this is what you want but try this =SUMPRODUCT(--(A1:A20="SHIFT"),B1:B20,C1:C20) HTH John "JANA" wrote in message ... I have 3 columns A, B, C. Column A has rows with either a blank cell, the word "shift" or the word "hazard". Columns B & C have numbers. I want the formula to look at a range in column A (A1:A20), and if a row has the word shift, to multiply columns B & C for the same range. I tried the following formula with no luck =IF(A1:A20="shift",SUMPRODUCT(B1:B20,C1:C20),0) Obviously, not the correct formula for this situation. Any help would be greatly apprectied! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If & sumproduct formula with text
Thank you, that worked. One more twist I want to see if excel can do. I
created Column A with just the word "shift" or "hazard", however it really should have a labor category name with "- shift" or "- hazard" at the end. Can the formula be changed so instead of searching for the word shift in the column, to search for the word at the end of a sentence instead? "John" wrote: Hi Jana Not sure if this is what you want but try this =SUMPRODUCT(--(A1:A20="SHIFT"),B1:B20,C1:C20) HTH John "JANA" wrote in message ... I have 3 columns A, B, C. Column A has rows with either a blank cell, the word "shift" or the word "hazard". Columns B & C have numbers. I want the formula to look at a range in column A (A1:A20), and if a row has the word shift, to multiply columns B & C for the same range. I tried the following formula with no luck =IF(A1:A20="shift",SUMPRODUCT(B1:B20,C1:C20),0) Obviously, not the correct formula for this situation. Any help would be greatly apprectied! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If & sumproduct formula with text
Hi,
Try this =sumproduct((isnumber(search("Shift",A1:A20,1))*(B 1:B20)*(C1:C20)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "JANA" wrote in message ... Thank you, that worked. One more twist I want to see if excel can do. I created Column A with just the word "shift" or "hazard", however it really should have a labor category name with "- shift" or "- hazard" at the end. Can the formula be changed so instead of searching for the word shift in the column, to search for the word at the end of a sentence instead? "John" wrote: Hi Jana Not sure if this is what you want but try this =SUMPRODUCT(--(A1:A20="SHIFT"),B1:B20,C1:C20) HTH John "JANA" wrote in message ... I have 3 columns A, B, C. Column A has rows with either a blank cell, the word "shift" or the word "hazard". Columns B & C have numbers. I want the formula to look at a range in column A (A1:A20), and if a row has the word shift, to multiply columns B & C for the same range. I tried the following formula with no luck =IF(A1:A20="shift",SUMPRODUCT(B1:B20,C1:C20),0) Obviously, not the correct formula for this situation. Any help would be greatly apprectied! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 criteria lookup of text. Return text form column 3. SUMPRODUCT t | Excel Worksheet Functions | |||
Text in SUMPRODUCT | Excel Worksheet Functions | |||
sumproduct text if | Excel Worksheet Functions | |||
SUMPRODUCT AND TEXT INM FORMULA | Excel Worksheet Functions | |||
SUMPRODUCT vs Text??? | Excel Worksheet Functions |