Identify unique value, lookup in another column, then count distinct values in adjacent column.

M

MarkS1234

New Member
Joined
Dec 17, 2024
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Please see example below. ID and Animal will be entered in to worksheet. I have entered a formula to identify the unique IDs in Column A. But I then need to calculate how many different animals for each ID. The desired values are in red.
 

Attachments

  • Excel Formula Required.png
    Excel Formula Required.png
    17 KB · Views: 6

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
MarkS1234 said:
Please see example below. ID and Animal will be entered in to worksheet. I have entered a formula to identify the unique IDs in Column A. But I then need to calculate how many different animals for each ID. The desired values are in red.
Click to expand...

Excel Formula: 
=LET(d,CHOOSECOLS(UNIQUE(A2:B12),1),GROUPBY(d,d,COUNTA,0,0))
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Sum only the unique values from one column within a range of duplicate values from another column
Replies
7
Views
341
MARZIOTULLIO
M
J
  • Question Question
CountIFS and Sumif based on duplicate value in other columns
Replies
3
Views
376
jennybell
J
J
  • Question Question
VBA to merge 2 tables across many sheets
Replies
1
Views
156
alansidman
A
T
  • Solved
Multiple criteria COUNTIFS with distinct values
Replies
4
Views
1K
Fluff
Fluff
B
  • Solved
Creating Unique list from Customer Order Amount, Customer Name and Order Identifier using the Unique function
2
Replies
11
Views
366
etaf
etaf

Forum statistics

Threads
1,224,698
Messages
6,180,423
Members
452,981
Latest member
MarkS1234

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top