CountIf Question

**hodkd** · 04-13-2009, 04:54 PM

I have a spread sheet to keep track of work schedules and I use a Capital letter to show a day 12 hour shift and a small letter to show a night 12 hour shift. I set up a countif formula to let me know if there were at least two of the same letters in the same day. That works okay but it counts them the same if they are both capitals or small letters. Would there be some way for the formula to count them only if there is both a small and capital letter and not if there are only small or capital letters?

Jan
1 2 3 4
H H H h
h h h h
2 2 2 2 This is where the countif formula is and I would like it to not count unless there is both a capital and small letter not two of each.

See attached sheet for a better sample

**c_m** · 04-13-2009, 06:18 PM

Try in A5:
=IF(CODE(A3)<>CODE(A4),2,"")
and copy across

**hodkd** · 04-13-2009, 06:26 PM

Thanks, this seems to work. Can this be used in the conditional format to change the color of the cell if it does equal 2?

**hodkd** · 04-13-2009, 06:31 PM

I used a conditional format to tell it to turn red if it equals 2 which works perfectly. Thanks

**JBeaucaire** · 04-13-2009, 06:32 PM

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

**hodkd** · 04-13-2009, 06:37 PM

That works for the sample I attached but I need to send a larger sample for you to look at.

**c_m** · 04-13-2009, 06:43 PM

Originally Posted by hodkd

That works for the sample I attached but I need to send a larger sample for you to look at.

So, I guess the solution didnt work for the larger sample...Post your larger sample then.

**hodkd** · 04-13-2009, 07:02 PM

I posted a larger sample in the orginal post.

**c_m** · 04-13-2009, 07:33 PM

Try in B12:
=IF(CODE(INDEX(B$3:B$10,MATCH(LEFT($A12,FIND("'",$A12)-1),B$3:B$10,0)))<>CODE(INDEX(B$3:B$10,MATCH(LEFT($A12,FIND("'",$A12)-1),B$3:B$10,0)+1)),2,"")

copy down and across

(and btw, C13 should be empty in your expected result)

**hodkd** · 04-29-2009, 02:13 PM

Sorry I was on vacation and didn't get back to the post till today. I put in the formula you suggested and it seems to work on some colums and not others. See attachment of actual spreadsheet. Any suggestions?