2

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

Mark Robles
  • 23

1 Answers1

1

If I understand your request correctly, the following formulas will work.  My solution requires a dummy column and a helper row, but they can be hidden.  Further, you can probably eliminate the dummy column with a little bit of work, and you can put the helper row anywhere.

I’ll assume that your client names are in row 2.  I’ll use column A as the dummy column, so the appointment data start in column B.  Row 3 will be the helper row.  In cell B3, enter the formula:

=IF(B2="", "", IF(B2<>C2, 1, C3+1))

Meaning:

  • If B2="", the client name for this timeslot is blank, so this is an idle timeslot, so display blank.
  • Otherwise, if B2<>C2, this timeslot and the next have different clients (C2 may or may not be blank), so this is the last timeslot for this appointment.  Represent it as 1.  Otherwise, count backwards, so the second-to-last timeslot for this appointment is 2, the third-to-last timeslot is 3, etc.

In cell B4, enter:

=IF(A2<>B2, B3, "")
  • If A2<>B2, this timeslot and the previous one have different clients (A2 may or may not be blank), so this is the first timeslot for this appointment.  Display B3, which shows how many timeslots (half hours) there are in this appointment.  Otherwise, display blank.

Here’s your data with these formulas:

       
                (The above image links to a complete one.)

I changed the GUAVA data to better illustrate how this works:

                                                               

You say, “The value must occupy at least two cells ….”  If I understand that correctly, you can change the formula in row 4 to:

=IF(AND(A2<>B2,B3>1), B3, "")

i.e., display the B3 value only if it is > 1. This shows the first row 4 formula in row 4 and the modified one in row 5:

       

G-Man Says 'Reinstate Monica'
  • 8,029