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.