Formula to calculate the number of coincidences between two sheets

[Héctor G. Iglesias]

Hi,

I have a spreadsheet file which contains purchases and sales.

The Purchases sheet contains these data, starting in column B (purchased by indicates the user who bought the player):

----------|--------------|----------------------------|---------------------|---------------------|------------------------
DATE | PLAYER | PURCHASED BY | PAID VALUE | DAY VALUE | SEARCH DATA

----------|--------------|----------------------------|---------------------|---------------------|------------------------

The Sales sheet contains these data, also starting in column B (sold by indicates the user who sold the player):

----------|--------------|----------------|----------------------|--------------------|---------------------------
DATE | PLAYER | SOLD BY | SALE VALUE | DAY VALUE | SEARCH DATA

----------|--------------|----------------|----------------------|--------------------|---------------------------

In both the purchases and sales tables, search data is the concatenation of all values, except for the purchased by/sold by cell (column D). If a transfer is made between players, there will be a coincidence in date, player, paid/sale value and day value in both sheets. Although it's not used at the moment, I thought it would be useful from another perspective.

My goal is to store the number of those coincidences between users:

---------------------------------------------|-----------|-------------|------------|---------------
PURCHASED BY \ SOLD BY | User 1 | User 2 | User 3 | User 4
---------------------------------------------|-----------|-------------|------------|---------------
User 1                                       |            |               |             |     

---------------------------------------------|-----------|-------------|------------|---------------

User 2                                       |            |               |             |     

---------------------------------------------|-----------|-------------|------------|---------------
User 3                                       |            |               |             |     

---------------------------------------------|-----------|-------------|------------|---------------

User 4                                       |            |               |             |     

---------------------------------------------|-----------|-------------|------------|---------------

This is what I've tried so far, but 0 is returned in both formulae:

=COUNTIFS(
    PURCHASES!$B:$B; SALES!$B:$B;
    PURCHASES!$C:$C; SALES!$C:$C;
    PURCHASES!$D:$D; $B3;
    SALES!$D:$D; C$2;
    PURCHASES!$E:$E; SALES!$E:$E;
    PURCHASES!$F:$F; SALES!$F:$F)

=SUMPRODUCT(
 (PURCHASES!$D:$D=$B3)*
 (SALES!$D:$D=C$2)*
 (PURCHASES!$G$:$G$=SALES!$G:$G)
)

In the latter formula, column G points to the stored search data.

I've also tried merging both sheets into another one to use a pivot table, but I don't know how to calculate the number of coincidences in the pivot table.

Thanks in advance for any help.