Excel countifs any random cell from given range has the same value-CodePudding

Not sure how to formulate my question so I'll start with an example. I have a dataset that looks like this (the whole dataset has ~150000 rows) - highlighted some of the values for easier viewing:

What I want to do is count how many times "Johnson, Jim, 10" and "Gordon, Tom, 15" appear in the same SUBDATA set where the first column cells have the same value.

I counted how many times the respective values appear in the whole data set separately using COUNTIFS:

But what I want to do is somehow counting how many times both iterations of the given data ("Johnson, Jim, 10" and "Gordon, Tom, 15") appear IF the first column value is the same (the value should be a DATE in the format YYYYMMDD).

I don't want to give the formula a date e.g. 20221005 and see if those 2 instances appear together because I have hundreds of dates. I just want to somehow tell the formula to CHECK when it finds any of those two records if there is an instance where the DATE is the same and count it.

e.g. If "Johnson, Jim, 10" and "Gordon, Tom, 15" have their respective related A cell with the same value (20221010), count it. And see how many times this happens in the whole dataset.

I would like to know if it's possible to do this using only formulas, without using Macros.

PS: Sorry if I didn't make myself clear enough, I will answer every question you have.

CodePudding user response：

Here is a long way round:

Formula in K2:

=LET(x,FILTER(A1:A12,BYROW(B1:D12,LAMBDA(a,SUM(--(MMULT(--(a=H1:J2),{1,1,1})=3))))),COUNT(UNIQUE(FILTER(x,MAP(x,LAMBDA(y,SUM(--(x=y))))=2))))

CodePudding user response：

Assuming within each date group there are no duplicated values (if not the formula can be adjusted), then you can use the following formula in I3:

=LET(dates, A1:A12, values, B1:B12&C1:C12&D1:D12, lkUp, F2:F3&G2:G3&H2:H3,
  REDUCE(0, UNIQUE(dates), LAMBDA(acc,ux, LET(
    set, FILTER(values, dates=ux), match, IF(COUNT(XMATCH(lkUp, set))=2, 1, 0), 
    acc   match)))
)

and here is the output:

What it does is for each unique dates it uses concatenation to find index positions via XMATCH. If both lookup values (lkUp) are found it is counted as 1, otherwise 0. REDUCE does the total sum of all matches.