I'd like the count the frequency of identical values in a row. The value must occupy at least two cells and must be beside each other.

I'd like to post the image here but i do not have enough reputations. Anyway, screenshot can be found here: https://www.dropbox.com/s/1em9ltssc1ruw0u/stackOverflow_excelIssue_Countfrequencyofsimilarvaluesinbetweenblankcells.jpg?dl=0

J7:BE7 is where a person will type in the values - it correspond to time, in 30mins increment.

On a different part of the same sheet, in this case DD7:EY7 is where i have the formulas running.

This formula is on (first column) DD7 -- COUNTIF(J7:$BE7,J7)

This formula is on (last column) EY7 -- COUNTIF(BE7:$BE7,BE7)

The formula on DD7 counts the number of times the value on J7 appears across row 7 from column J to BE.

scenario 1: This formula works great if "orange" will only appear once. on my example from K7:L7.

scenario 2: The problem is when "orange" appears again on a non consecutive cell. what i mean is, "orange" appears on K7:L7, then it appears again on P7:Q7.

on scenario 1, the result of the formula in checking K7:L7 is 2, which is the desired value.

on scenario 2, the result of the formula in checking K7:L7 is 4, because it will also count "orange" that appears on K7:L7.

I was hoping the counting will stop if M7 has a different value. K7:L7 contains "orange" and M7:N7 has "apple". or the counting will stop if there's an empty cell in between, like U7:V7 contains "guava", and T7 is empty, then X7:Y7 again has "guava".

the fruits may occupy two to forty-eight columns of each row.

I know this is wordy, but i can't find other ways to explain it, english is not my first tongue.

Thank you in advanced.

~mark

`K`

,`L`

,`P`

, and`BE`

) in your text when they aren’t visible in your image. (2) It’s a simple enough data set; you can just type in example data, and forget the image. (3) You aren’t talking aboutsimilarcells; you’re talking aboutcells. (4) Are youidenticalasking about blank cells? Based on your description of the real-world problem,really`AA__BBAA`

and`AABBCCAA`

should both be counted as two separate appointments for client`A`

– it doesn’t matter whether there’s a blank cell there or not. – G-Man Says 'Reinstate Monica' Sep 2 '14 at 23:28