How to count distinct instances of two columns when one column is a date

Question

I have a table like this:

Name	Date
Ann	2/1/2024
Bob	3/3/2024
Ann	2/1/2024
Bob	3/5/2024

I want to be able to tell what unique combinations of the two columns there are and if there are any repeats.

My method was to join the fields in a new field using =CONCAT(A2,TEXT(B2,"mm/dd/yyyy"):

Name	Date	combined
Ann	2/1/2024	Ann2/1/2024
Bob	3/3/2024	Bob3/3/2024
Ann	2/1/2024	Ann2/1/2024
Bob	3/5/2024	Bob3/5/2024

I then coped/pasted this combined column as value, so the formulas don't affect any future functions.

Then, I created a new column that should count the instances that each item occurs in column c =COUNTIF(C:C,C2):

Name	Date	combined_pastedasvalues	count
Ann	2/1/2024	Ann2/1/2024	2
Bob	3/3/2024	Bob3/3/2024	1
Ann	2/1/2024	Ann2/1/2024	2
Bob	3/5/2024	Bob3/5/2024	2

However, when I do this, it looks like the count isn't working properly (see the fourth row, it should only have a count of 1 when it's showing 2), and there are items returned with 12 instances of occurring, but when I go to check, it's the only instance in the dataset.

Any advice here on how to make this work? Thanks in advance.

Answer 1

Perhaps one can use the following with intermediate columns to accomplish the desired output:

---

• Formula used in cell C2

=A2:A5&"|"&TEXT(B2:B5,"m/d/e")

---

While in D2

=COUNTIF(C2#,C2)

---

Or, use the following with MMULT()

---

=LET(x,A2:A5& "|"&TEXT(B2:B5,"m/d/e"), MMULT(N(x=TOROW(x)),SEQUENCE(ROWS(x))^0))

---

Using ETA LAMBDA it will be shorter as well:

---

=LET(x,A2:A5& "|"&TEXT(B2:B5,"m/d/e"), BYROW(N(x=TOROW(x)),SUM))

---

Answer 2

Using a Table (named Table1) and structured references:

=SUM(
    --(
        CONCAT(Table1[@[Name]:[Date]]) =
            BYROW(
                Table1[[Name]:[Date]],
                LAMBDA(arr, CONCAT(arr))
            )
    )
)

Some advantages of using a Table:

• The formulas will auto-populate as you add/delete rows
• No references in the formula will also adjust to the size of the table.