software

Based on the following data I would like a formula in Column D that will
return a value based on data in columns a, b and c.
a b c
D
1 WC0256ITT OPEN $500.00 Internal
2 14A555SOLD $100.00 $100
3 WE0516ITT PARTIAL $200.00 Internal

The Column D values are the correct value. I tried this formula but I'm not
sure what's wrong with it.

SUMPRODUCT(--( ISERR(SEARCH(quot;ITTquot;,b1)),--(
ISerror(LEFT(a1) 0)),(c1)),quot;Internalquot;

THANK YOU

Further clarification, If the cells in column A begin with a letter and cells
in B contain the term quot;ITTquot;, Column D should read INTERNAL. If either/or the
criteria for A and B are not true, Column D should read the value of column C.

Sorry so confusing and THANK YOU for assistance.

quot;TMF in MNquot; wrote:

gt; Based on the following data I would like a formula in Column D that will
gt; return a value based on data in columns a, b and c.
gt; a b c
gt; D
gt; 1 WC0256ITT OPEN $500.00 Internal
gt; 2 14A555SOLD $100.00 $100
gt; 3 WE0516ITT PARTIAL $200.00 Internal
gt;
gt; The Column D values are the correct value. I tried this formula but I'm not
gt; sure what's wrong with it.
gt;
gt; SUMPRODUCT(--( ISERR(SEARCH(quot;ITTquot;,b1)),--(
gt; ISerror(LEFT(a1) 0)),(c1)),quot;Internalquot;
gt;
gt; THANK YOU

Sorry to be blunt, but you have totally misunderstood the use of SUMPRODUCT.
See www.xldynamic.com/source/xld.SUMPRODUCT.html

What you need is IF. Try this
=IF(AND(CODE(UPPER(LEFT(A1,1)))gt;=65,CODE(UPPER(LEF T(A1,1)))lt;=90,LEFT(B1,3)=quot;ITTquot;),quot;Internalquot;,C1)

To test more than one condition in IF we use the AND function.
=IF(AND(test1, test2, ....), true_reply, false_reply)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

quot;TMF in MNquot; gt; wrote in message
...
gt; Further clarification, If the cells in column A begin with a letter and
gt; cells
gt; in B contain the term quot;ITTquot;, Column D should read INTERNAL. If either/or
gt; the
gt; criteria for A and B are not true, Column D should read the value of
gt; column C.
gt;
LOL!! Be as blunt as you want! the Sumproduct was a desperate attempt to
blindly find my answer, but it failed miserably. Yours, however, worked
marvelously!
THANK YOU

quot;Bernard Liengmequot; wrote:

gt; Sorry to be blunt, but you have totally misunderstood the use of SUMPRODUCT.
gt; See www.xldynamic.com/source/xld.SUMPRODUCT.html
gt;
gt; What you need is IF. Try this
gt; =IF(AND(CODE(UPPER(LEFT(A1,1)))gt;=65,CODE(UPPER(LEF T(A1,1)))lt;=90,LEFT(B1,3)=quot;ITTquot;),quot;Internalquot;,C1)
gt;
gt; To test more than one condition in IF we use the AND function.
gt; =IF(AND(test1, test2, ....), true_reply, false_reply)
gt; best wishes
gt; --
gt; Bernard V Liengme
gt; www.stfx.ca/people/bliengme
gt; remove caps from email
gt;
gt; quot;TMF in MNquot; gt; wrote in message
gt; ...
gt; gt; Further clarification, If the cells in column A begin with a letter and
gt; gt; cells
gt; gt; in B contain the term quot;ITTquot;, Column D should read INTERNAL. If either/or
gt; gt; the
gt; gt; criteria for A and B are not true, Column D should read the value of
gt; gt; column C.
gt; gt;
gt;
gt;
gt;