Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Wobbled countif and conditional format Results
In sheet2 there is raw data spanning F2:CQ8359.
In sheet1 are the key ranges (no range named). A2:A8359 (exact order) and B2:F8359 (concatenations of exact order - less exact order) i.e. if A2 = ABC then B2:F2 = ACB, BAC, BCA, CAB, CBA repectfully. I need to display in sheet1 G6:CV8359 any cells in sheet2!F2:CQ8359 that match in exact order as in $A$2:$A8359 Or if it is not exact order then chec $B$2:$F8359 otherwise leave it blank (""). Therefore in sheet1 at G6 this is what I use: =IF(COUNTIF($A$2:$A10,Sheet2!F6),Sheet2!F6,IF(COUN TIF($B$2:$F10,Sheet2!F6),Sheet2!F6,"")). Noting that G6 is the center cell of 9 total cells/rows to check (Row G6 then 4 rows before and 4 rows after). I see the correct results with that formula. I then creat a Conditional format - 1st highlighting Sheet1 K2:CV8359, then Format; Conditional Format; entering the =COUNTIF($A$2:$A2,K2) to color "Green" only those that are in exact order, then add a 2nd condition of =COUNTIF($B$2:$F2,K2) to color "Yellow" only those that are in concatenation of exact order (less exact order). The issue I am having is when I do the conditional format I get some of the cells that are exact highlight yellow and some of the non exact cells highlight Green and some of the cells don't highlight at all. I don't get why it does that nor can I figure out what to do. I have checked in with websites like http://www.contextures.com/xlCondFormat03 and also here at communities but come up short with answers. I hope to have included enough information for you to savey. As always I am greatful for your help. Regards, Luke |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF provides weird results | Excel Worksheet Functions | |||
COUNTIF with pivot table results? | Excel Worksheet Functions | |||
Conditional Sum Argument results do not equal cell results Excel | Excel Worksheet Functions | |||
Multiple Countif results | Excel Worksheet Functions | |||
countif results | Excel Worksheet Functions |