Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumProduct with criteria list | Excel Worksheet Functions | |||
Multiple Criteria in SumProduct, N/A Result | Excel Worksheet Functions | |||
Using Sumproduct with multiple Criteria | Excel Worksheet Functions | |||
Sumproduct w/date criteria not working | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions |