If & sumproduct formula with text

[JANA]

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!

 

[John]

Hi Jana
Not sure if this is what you want but try this
=SUMPRODUCT(--(A1:A20="SHIFT"),B1:B20,C1:C20)
HTH
John

 

[JANA]

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?

 

[Ashish Mathur]

Hi,

Try this

=sumproduct((isnumber(search("Shift",A1:A20,1))*(B1:B20)*(C1:C20))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com