Count Unique Number Values When Data Filtered

CaitCampLand

New Member
Joined
Oct 14, 2024
Messages
6
Office Version
  1. 365
Platform
  1. Windows
I want to count the unique company IDs when the data in the table is filtered. Currently I am using this formula: =SUMPRODUCT(1/COUNTIF(A3:A42,A3:A42)). This works great but if I want to filter of course, it needs something else. I've tried several different options and am still at a loss. Hoping someone can help me here.
1728920268019.png
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Hi & welcome to MrExcel.
How about
Excel Formula:
=ROWS(UNIQUE(FILTER(A2:A1000,MAP(A2:A1000,LAMBDA(m,SUBTOTAL(103,m))))))
 
Upvote 0
Solution
Hi & welcome to MrExcel.
How about
Excel Formula:
=ROWS(UNIQUE(FILTER(A2:A1000,MAP(A2:A1000,LAMBDA(m,SUBTOTAL(103,m))))))
That didn't work and only gave me five when there are actually 31 :(. Thank you so much for trying to help me.
 
Upvote 0
In that case can you post some sample data that shows the problem.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
In that case can you post some sample data that shows the problem.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
Thank you! I'll do that.
 
Upvote 0
Hi & welcome to MrExcel.
How about
Excel Formula:
=ROWS(UNIQUE(FILTER(A2:A1000,MAP(A2:A1000,LAMBDA(m,SUBTOTAL(103,m))))))
I think I got it to work once I changed the criteria range in your code, however, it counts zero cells. Not that it is a big issue because there is only ever one zero but is there a way to add to your code and omit cells that are zero?
 
Upvote 0
Do you mean you will have a cell with 0, do doy ou mean sells that are totally empty?
 
Upvote 0
Ok, how about
Excel Formula:
=ROWS(UNIQUE(FILTER(A2:A1000,(a2:a1000<>0)*(MAP(A2:A1000,LAMBDA(m,SUBTOTAL(103,m)))))))
 
Upvote 0

Forum statistics

Threads
1,224,811
Messages
6,181,080
Members
453,021
Latest member
Justyna P

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