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

  • Please try to make this clearer. (1) It doesn’t help to give column identifiers (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 about similar cells; you’re talking about identical cells. (4) Are you really asking about blank cells? Based on your description of the real-world problem, 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
  • I believe you might be able to monkey something together by using a combination of nested ifs to create a unique number for each individual appointment period/client, and then use a rank formula so you can then vlookup distinct, matching values within each set, but that sounds absolutely pitifully awful to manage. This is really best solved using VBA/Macro. – JNevill Sep 3 '14 at 15:15
  • @g-man, i updated the image to show grid. thank you, yes, i should have used the word identical. yes, AA__BBAA and AABBCCAA should be counted as two different appointments for client A. but i want to count the lenght of booking of client A. using countif, the result will be 4 on the first appointment, and 2 on the next appointment. i need it to show 2 on both instance. – Mark Robles Sep 3 '14 at 22:06
  • @jnevill, yes, i agree, VBA is the best way to go. someone shared me his formula, stackoverflow.com/questions/25632952/… but the result of the formula doesn't update unless i copy and paste his formula again. or each time that there's an update on my data. i'm noob in VBA, and i'm not sure how to solve the "non-updating" issue. =( – Mark Robles Sep 3 '14 at 22:08
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:

       

  • thank you very much! this is exactly the answer to my question. this gives me exactly the result i need. if you ever drop by manila, philippines, let me know so I can buy you a beer. – Mark Robles Sep 4 '14 at 7:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.