I have the following summation table

Range 1 (expected output)

          (column A)                                                         (Column D)
 Rows   Responsibility Center         | Authorized HC   | # of Vacancies|   Pending Approvals
 2       County 1                    |         26       |         1     |          2
 3       County 2                    |            7     |        21     |          1 
 4       County 3                    |       53         |        35     |          1 
 5       County 4                    |       53         |        35     |          0 

Range 2

     (column a)                                                   (Column d)
Rows  Create Date|  Incumbent Name      |   Office Title        |   Responsibility Center
18    5/23/2024  |John Jones              | Housing Assistant    |  County 1
19    5/23/2024  | Jane Joe               | Housing Assistant    |  County 2
20    5/28/2024  |  Jim Jong            | Housing Assistant     |   County 3
21    5/28/2024  |Cang Cong               |Housing Assistant    |   County 1

Basically the pending approvals in range 1 need to be drawn from the responsibility center column in range 2. So there are values for county 1 2 and 3, but not 4, so I don’t expect any value to show up in that cell. The list in range two can be dynamically sized, so I’d prefer to not hardcode the range in if I can.

I tried the following two functions in column d, copy pasted down
in d2:d5

= countif(d18#, $A2)
=MAP(A5#, CHOOSECOLS(A18#, 4), LAMBDA(a,b, COUNTIF(a, b)))

both didn’t work!