1

I have a column of (text) values. I want to count the number of times a non-blank cell arises such that its value differs from that of the cell immediately above it. I do not want just a sheer count of unblank cells or of the total number of cells minus the number of blank ones.

For example, if the column is of form (A, B, B, A, A, A, _, A, B, C, B), where "A" and "B" and "C" represent some text (such that they are distinct from one another) and "_" (underscore) represents a blank cell, the count that I want would be 7 because:

  • The "A" in the first entry counts.
  • The "B" in the second entry counts.
  • The "B" in the third entry does not count.
  • The "A" in the fourth entry counts.
  • The "A"s in the fifth and sixth entry do not count, each.
  • The "_" in the seventh entry does not count because it is blank.
  • Each subsequent entry counts because each is non-blank and different from the immediately preceding entry.

How do I make such a function?

Improve this question
1
  • Some sort of CountIf function will probably be used, but I am not sure how to make the condition loop down the entire column (and I also do not know how to avoid counting blank cells).
    – user173897
    Commented Jan 30, 2018 at 7:51

2 Answers 2

Reset to default
3

You can try the below, let me know how it works out, in theory on that data set i got 7 but not 100% sure I understood all criteria.

=1+SUMPRODUCT(--($A$1:$A$11<>OFFSET($A$1:$A$11,1,0)),--(OFFSET($A$1:$A$11,1,0)<>""))
Improve this answer
8
  • I think that that worked! I have been testing it out and it seems fine. Thanks!
    – user173897
    Commented Jan 30, 2018 at 8:06
  • Would you like me to re-explain the goals/criteria, though, for any reason?
    – user173897
    Commented Jan 30, 2018 at 8:07
  • @user91504, I've tested and this one is producing ZERO in both cases, if the Data Range has a blank Cell or Underscore is written.
    – Rajesh Sinha
    Commented Jan 31, 2018 at 7:23
  • hi @RajeshS which cases are your referencing ? the OP only gave one example
    – PeterH
    Commented Jan 31, 2018 at 11:19
  • @User91504, I've applied your Formula to test on OP's data, produces Zero, and on the same data I've tested my Formula Sum(if(Frequency(,,,, is counting properly.
    – Rajesh Sinha
    Commented Jan 31, 2018 at 12:01
-1

The below written Formula will help to Count, as OP said the NON REPEAT values in Data Range.

{=SUM(IF(FREQUENCY(IFERROR(MATCH(A2:A12,A2:A12,0),""),ROW(A2:A12)-ROW(A2)+1)=0,1))}

NB: I've modified the previous Formula and included IFERROR because OP has mentioned that UNDERSCORE is a BLANK CELL.

And now the Formula is CSE, so finish it with Ctrl+Shift+Enter.

Note, my below written previous Formula will work without IFERROR, if in place of Blank Cell Underscore is written.

=SUM(IF(FREQUENCY(MATCH(A2:A12,A2:A12,0),ROW(A2:A12)-ROW(A2)+1)=0,1))

Hope this help you.

Improve this answer
2
  • This formula gives #N/A as a result. Presumably because the MATCH() function returns an array with #N/A corresponding to the blank in OP's example data. Correcting that with IFERROR(): =SUM(IF(FREQUENCY(IFERROR(MATCH(A1:A11,A1:A11,0),""),ROW(A1:A11)-ROW(A1)+1)=0,1)) at least gives a numerical answer of 9. But that answer is incorrect. Maybe you would get less downvotes if you actually tested the formulas you post?
    – Bandersnatch
    Commented Jan 30, 2018 at 18:39
  • @Bandersnatch, while test the Formula I've inserted UNDERSCORE sign in the Data Range so that my Formula was not producing error. But since OP has assumed UNDERSCORE sign as BLANK in this case IFERROR is required. Let me edit the Formula. Thanks for positive feedback.
    – Rajesh Sinha
    Commented Jan 31, 2018 at 7:04

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .