2

enter image description here

In cell C3 I count the number of unique values in column A:

=SUM(IF(FREQUENCY(A2:A12,A2:A12)>0,1,0))

I would like to know; how many events were a 'first contact'. That means I only want to count one 'yes' per event, not all 'yes' that I have in column B. The correct answer is 4 not 9.

I am not able to connect those two conditions, to only count yes when unique event number.

Is anyone able to help me with a formula for that, please?

Thank you very much! Tanja

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tanja
  • 21
  • 1

3 Answers3

4

You could use:

enter image description here

Formula in D2:

=SUM(N(UNIQUE(A2:B12)="yes"))
JvdV
  • 70,606
  • 8
  • 39
  • 70
3

You could try something along the lines:

enter image description here

• Formula used in cell D4

=ROWS(UNIQUE(FILTER(A2:A12,B2:B12<>"no")))

Alternative approaches:

enter image description here

• Formula used in cell E2

=COUNT(--UNIQUE(REPT(A2:A12,B2:B12="yes")))

• Formula used in cell F2

=ROWS(UNIQUE(TOCOL(IF(B2:B12="no",NA(),A2:A12),3)))

• Formula used in cell G2

=SUM(N(FREQUENCY(IF(B2:B12<>"no",A2:A12,""),A2:A12)>=1))
Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
  • 1
    Mayukh, your first approach is what I had in mind. It is larger than JvdV's formula, but it is **faster**. Filtering first reduces the dataset that would the be input for `UNIQUE`. JvdV's solution does `UNIQUE` first, which results in a higher computational effort. I tested both for `100K` random rows and it confirms what I suspect, your solution is almost `3x` faster (`20ms` vs `70ms`). – David Leal Jun 24 '23 at 19:21
  • 1
    Sounds Good Sir. But @JvdV Sir's solution is sleek & short and like it very much **(Sir's solutions are always out of the box)**. Can you teach me how do you test the performance, last time you said, I couldn't make that out. Also `FREQUENCY()` function is slow, posted since not sure about `OP's` version. – Mayukh Bhattacharya Jun 24 '23 at 19:24
  • 1
    Agreed, @JvdV is an Excel magician. I shared the source file in the following [answer](https://stackoverflow.com/questions/76426929/excel-dynamic-sum-formula-0-1/76428564#76428564), where I have the functions for doing performance tests. Look for the function: `lib.PERF_MSTEST`. I hope it helps if not let me know. Thanks – David Leal Jun 24 '23 at 19:29
  • 1
    another point, but I am not sure about that. `ROWS`, probably reads a property from an excel object internally, rather than calculating the number of rows, so it would performa better than `SUM` for example. – David Leal Jun 24 '23 at 19:39
  • 1
    This was my initial solution in the comments `=ROWS(UNIQUE(MMULT((A2:A12&"|"&B2:B12=TOROW(A2:A12&"|"&B2:B12))*(B2:B12<>"no"),SEQUENCE(ROWS(A2:A12),,,0))))`. I deleted that posted the one with `ROWS()` – Mayukh Bhattacharya Jun 24 '23 at 19:42
  • 1
    I can confirm that `ROWS` is faster than `SUM`, I did some quick test. – David Leal Jun 24 '23 at 19:44
0

Then there is this version:

=COUNTIFS(A$2:A$12,@UNIQUE(A$2:A$12),B$2:B$12,"yes")

enter image description here

Solar Mike
  • 7,156
  • 4
  • 17
  • 32