Count Unique Number Values When Data Filtered

[CaitCampLand]

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.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=ROWS(UNIQUE(FILTER(A2:A1000,MAP(A2:A1000,LAMBDA(m,SUBTOTAL(103,m))))))

 

[CaitCampLand]

Hi & welcome to MrExcel.
How about

=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.

 

[Fluff]

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.

 

[CaitCampLand]

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.

 

[CaitCampLand]

Hi & welcome to MrExcel.
How about

=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?

 

[Fluff]

Do you mean you will have a cell with 0, do doy ou mean sells that are totally empty?

 

[CaitCampLand]

Do you mean you will have a cell with 0, do doy ou mean sells that are totally empty?

A cell that has a zero

 

[Fluff]

Ok, how about

=ROWS(UNIQUE(FILTER(A2:A1000,(a2:a1000<>0)*(MAP(A2:A1000,LAMBDA(m,SUBTOTAL(103,m)))))))

 

[CaitCampLand]

Ok, how about

=ROWS(UNIQUE(FILTER(A2:A1000,(a2:a1000<>0)*(MAP(A2:A1000,LAMBDA(m,SUBTOTAL(103,m)))))))

Genius! Thank you so much for all the help.