Tryring to find right formula

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

What I have is 5 columns of data
A=Part number B=Cost C=Qty D=Quoted E=Comments
123 1.00 1 History
456 .50 7 History
123 1.50 1
789 2.50 5 History
123 .75 1
456 1.00 7

What I'm trying to do is if the word History is in the
Comments column use the Part number to see if the same
part number exists elsewhere on the spreadsheet without
History in the Comments, if so say yes in the Quoted
column but only in the row that says History. Is this
possible to do? I tried using IF(E2="History",MATCH
(A2,A2:A7),D2,"Yes") but couldn't figure how to get the
thing tot work. Any help is appreciated.
TIA
Joe
 
Enter this as an array formula and see if it does what youd like

=+IF(D1="History",
IF(SUM(($A$1:$A$6=$A1)*($D$1:$D$6<>"History"))>0,"Yes",""),"")
 
Joe,

The following formula should work

=IF(E2="History",IF(SUMPRODUCT(($A$2:$A$7=A2)*($E$2:$E$7<>"History"))>0,"Yes","No"),"")

Explanation
SUMPRODUCT(($A$2:$A$7=A2)*($E$2:$E$7<>"History"))>0
Counts the number of items with column A equal to the
number in A2 AND "History" NOT in column E
If that number is bigger than 0 (ie their is such an item) then
it returns a "Yes" if not it returns a "No". If "History" is not
in column E it returns "" (nothing)

Dan E
 
Sorry should be E in place of D there.


Dave R. said:
Enter this as an array formula and see if it does what youd like

=+IF(D1="History",
IF(SUM(($A$1:$A$6=$A1)*($D$1:$D$6<>"History"))>0,"Yes",""),"")
Click to expand...
 
Back
Top