Count Unique Text based on criteria

[tourless]

Hi Folks.

Working with about 1000 rows of data, I have numeric values in column D not sorted but recurring at random intervals. Column A contains names. Those names can and will be repeated.

I'm looking for a way to say if the cell in column D is equal to 000415 then count the unique text values in column A. I've tried =COUNT('[My File 2022.xlsx]WE 05-28-22'!$D$6:$D$1500,"000415",'[My File 2022.xlsx]WE 05-28-22'!$A$6:$A$1500) and while it doesn't return an error, it also does not return the expected value either. In this instance I know my value should be 31, I'm returned with a value of 1. I also tried COUNTIF but I'm told it's too many parameters for the function.

Can someone help point me in the right direction? I'm not sure what I'm doing wrong.

 

[alansidman]

This may be easily accomplished with Power Query. Show us a sample of your data and your expected result for the sample presented. Please use XL2BB to show your sample data.

 

[Fluff]

How about

=ROWS(UNIQUE(FILTER('[My File 2022.xlsx]WE 05-28-22'!$A$6:$A$1500,'[My File 2022.xlsx]WE 05-28-22'!$D$6:$D$1500="000415")))

 

[tourless]

How about

=ROWS(UNIQUE(FILTER('[My File 2022.xlsx]WE 05-28-22'!$A$6:$A$1500,'[My File 2022.xlsx]WE 05-28-22'!$D$6:$D$1500="000415")))

That's a nifty little formula right there. Thanks Fluff!

 

[tourless]

This may be easily accomplished with Power Query. Show us a sample of your data and your expected result for the sample presented. Please use XL2BB to show your sample data.

Thanks for chiming in here Alan, Fluff got me sorted with a formula that works well for my need.

 

[Fluff]

Glad we could help & thanks for the feedback.