Test 1.xlsx
Hello.
In the above attachment, I have Column A,B and C.
Column D is the result that I would like to obtain.
For a given Return Ord number, if there are 1's on both columns B and D, I would like a 1 on Column D for a unique Return Ord Num.
Similarly, for a given Return Ord number, if there are 0's on both columns B and D, I would like a 0 on Column D for a unique Return Ord Num.
Much appreciate!
Give this one a try
=--(COUNTIFS(A:A,A2,B:B,0,C:C,0)=0)
edit:- because you have merged cells of different sizes you will not be able to copy the formula down without unmerging them first.
If the file that Imran has attached to post #3 is acceptable then either the formula used there, or the one I have provided here will work (see my comments to Imran in post 4 before deciding).
If you need it so that it only shows 1 result for each change of entry in column A, then try this one instead. Please note that if your table is not sorted by column A then you will get 1 result each time the entry in column A differs from the 1 above it regardless of whether or not that entry has appeared previously in the list.
=IF(A2=A1,"",--(COUNTIFS(A:A,A2,B:B,0,C:C,0)=0))
For 1 result per unique entry in column A with unsorted data, try
=IF(MATCH(A2,A:A,0)=ROW(),--(COUNTIFS(A:A,A2,B:B,0,C:C,0)=0),"")
Last edited by jason.b75; 12-19-2015 at 06:15 AM.
Hi,
PFA excel file.
regards Imran.
@Imran,
Your method is just a longer version of what I have suggested. When the desired result is either 1 for true or 0 for false you can omit the use of 'IF' by 1 of many simple methods.
=--(logical test)
=(logical test)+0
=(logical test)*1
Gives just 3 examples, there are other variations as well.
In addition, check the results of your formula, using =IF(logical test,"1","0") is bad practice, you should use =IF(logical test,1,0) instead (i.e. no "" when the return values are numeric).
To understand why, check the result of =SUM(D2:D25) using both methods.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks