Sumproduct with 2 criteria
 

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 WC0256 ITT OPEN $500.00 Internal
2 14A555 SOLD $100.00 $100
3 WE0516 ITT 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("ITT",b1)),--(
ISerror(LEFT(a1)+0)),(c1)),"Internal"

THANK YOU

Sumproduct with 2 criteria
 

Further clarification, If the cells in column A begin with a letter and cells
in B contain the term "ITT", 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.

"TMF in MN" wrote:

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 WC0256 ITT OPEN $500.00 Internal
2 14A555 SOLD $100.00 $100
3 WE0516 ITT 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("ITT",b1)),--(
ISerror(LEFT(a1)+0)),(c1)),"Internal"

THANK YOU

Sumproduct with 2 criteria
 

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

What you need is IF. Try this
=IF(AND(CODE(UPPER(LEFT(A1,1)))=65,CODE(UPPER(LEF T(A1,1)))<=90,LEFT(B1,3)="ITT"),"Internal",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

"TMF in MN" wrote in message
...
Further clarification, If the cells in column A begin with a letter and
cells
in B contain the term "ITT", 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.

Sumproduct with 2 criteria
 

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

"Bernard Liengme" wrote:

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

What you need is IF. Try this
=IF(AND(CODE(UPPER(LEFT(A1,1)))=65,CODE(UPPER(LEF T(A1,1)))<=90,LEFT(B1,3)="ITT"),"Internal",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

"TMF in MN" wrote in message
...
Further clarification, If the cells in column A begin with a letter and
cells
in B contain the term "ITT", 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.