How to count unique values that are present in 2 different sheets only if they exist in both sheets

Question

[Goal]

I want to be able to count unique values that are present in 2 different sheets only if they exist in both sheets.

[Details]

First, there are 2 sample data sheets (Data A, Data B) within the same Spreadsheet and it also has a sample dashboard to do the calculations. One thing to note about the data sheets is that they have different ranges, so they have different number of columns and rows. However, a couple things they have in common are the Month and ID columns.

Next, in the Dashboard sheet, there are 3 cells where B3 is the Month selector, C3 is to count the number of unique IDs that are included in both sheets (Data A & Data B) based on the month. With D3, I would like to count the number of unique IDs that are included in both sheets (Data A & Data B) where the the Month are the same AND Data B sheet's Cumulative column is 1.

[What I tried so far]

I tried using the COUNTUNIQUEIFS function and had the range as an array (by using the curly bracket) in the below way, however, it didn't work.

=COUNTUNIQUEIFS('Data A'!$B:$B,'Data A'!$A:$A,B3,'Data B'!$A:$A,B3)

I also tried without making the range argument an array.

=COUNTUNIQUEIFS('Data A'!$B:$B,'Data B'!$A:$A,B3)

Both attempts results in 1 for February 2023 when it should return 2. The weird thing is that January 2023 should return 3 and it correctly returns 3.

Hope someone can help me out with this. If there's a more elegant solution to achieve this by using Google Apps Scripts, I'd like to see hear about that as well.

Answer 1

You can try filtering both ranges for month, and the FILTER again by comparing them with MATCH and counting the remaining results:

=LAMBDA(ar,br,COUNTA(IFERROR(FILTER(ar,NOT(ISERROR (MATCH(ar,br,0))))))
(FILTER('Data A'!B:B,'Data )A'!A:A=B3),FILTER('Data B'!B:B,'Data B'!A:A=B3))

And just add an additional filter for the next column and the cumulative =1 'Data B'!C:C=1

=LAMBDA(ar,br,COUNTA(IFERROR(FILTER(ar,NOT(ISERROR (MATCH(ar,br,0)))))) (FILTER('Data A'!B:B,'Data )A'!A:A=B3),FILTER('Data B'!B:B,'Data B'!A:A=B3,'Data B'!C:C=1))