CODEWITHSUNDEEP
excel-formula
Megan
Megan

Reputation: 1

Excel question about counting the number of unique IDS when the value in a column matches the value in another column

I need a formula to count the number of unique IDS when the value in a column matches the value in another column.

Column A - Year | Column B - Number of Unique Firms | Column D - Firm ID Number | Column E - Year 2

For B2 I want a formula that says, check column D for the number of unique firms that had a transaction in the year (Year 2 column) that matches the value in A2. Basically give a count of all the firms that had a transaction in 2013.

I am trying to generate the values that are highlighted in the above picture in yellow.
In the actual data, Columns D and E are in a separate spreadsheet and there are over 150 thousand rows.

Upvotes: 0

Views: 281

Answers (2)

bosco_yip
bosco_yip

Reputation: 3802

Formula solution without helper column

In B2, enter formula :

=SUMPRODUCT((E$2:E$15=A2)/(COUNTIFS(D$2:D$15,D$2:D$15,E$2:E$15,E$2:E$15)))

enter image description here

Upvotes: 1

RetiredGeek
RetiredGeek

Reputation: 3168

I don't know what your limitations are, e.g. VBA, Helper Columns, etc. But here's a solution using helper columns.

You need 3 formulas:

  1. Find the UNIQUE Firm IDs: G4 =UNIQUE(D3:D16)
  2. Count the entries by year for each unique Firm ID: H3 =COUNTIFS($D$3:$D$16,$G3,$E$3:$E$16,H$1)
  3. Tally the entries for each year: B3 =COUNTIF(OFFSET(H$3:H$5,0,ROW()-3),"<>"&0)

You need to setup your helper columns as shown here: enter image description here

Place formula 1 in G4 after adjusting the references, which I'd highly recommend you to change to dynamic range names. There is no need to do anything further with this formula!

Next, place formula 2 in H3 and then fill down and across, again using dynamic range names.

Lastly place formula 3 in B3 and fill down. Once again using dynamic range names.

The result: enter image description here HTH

Upvotes: 0

Related Questions