Looking for Excel formula to count duplicates based on multiple criteria

Question

I am working with a large set of data (over 15000 rows).The example below is a very simplified version of what I am working with.

I am trying to count how many IDs appear both in 2023 and 2021, based on 2 criteria : CODE 83032 and UTIL 1000 (see sample data below).

I need a formula which will reside in a single cell; also, the data will never be sorted.

example

In this example, so far I've only managed to count based on one year (i.e. 2023):

=countifs(B2:B21,83032,C2:C21;1000,D2:D21,2023))

or =sumproduct((B2:B21=83032) * (C2:C21=1000) * (D2:D21=2023)*1)

• but I don't know how to add in the formula to look if the SAME ID appears also in the other year (i.e. 2021).

I really hope someone can help me with this!

Answer 1

I found a formula that works :

=SUMPRODUCT(--(ISNUMBER(MATCH(CHOOSECOLS( FILTER(A:D,(B:B=83032) * (C:C=1000) * (D:D=2021)),1), CHOOSECOLS(FILTER(A:D,(B:B=83032) * (C:C=1000) * (D:D=2023)),1),0))))

I basically ended up creating 2 arrays with the filter function (one with the 2021 data and the other with the 2023 data), extracted the first column of each with the choosecols function to get 2 arrays containing the list of IDs matching each set of criteria, and compared both arrays with the match function.

Answer 2

This formula works in my example.

=SUMPRODUCT( ( (D1:D16=2023) + (D1:D16=2021) ) * (C1:C16=1000) * (B1:B16=83032))

((D1:D16=2023)+(D1:D16=2021)) checks if column D is either 2023 or 2021. " + " is the OR operator in this formula while " * " represents AND.