Count Distinct with multiple criteria excel 2016 Pro Plus

gvanhook
StarLounger
Posts: 67
Joined: 22 Feb 2010, 20:15
Location: Indiana

Count Distinct with multiple criteria excel 2016 Pro Plus

Post by gvanhook »

Good day loungers,

I have a worksheet where I need to supply a distinct count based on two criteria. Column A will contain either numbers or NA. Column B will contain N or C. I need to know how many distinct numbers there are in A where column B equals "N". The count should ignore the NA rows in column A.

My preference is to use a formula for this count as the number of rows in the spreadsheet will grow daily.
I have used the following formula to get a distinct count of just one column, but do not know how to adapt it to use two columns.
=SUMPRODUCT(1/COUNTIF(A18:A751,A18:A751 & ""))-1
I want to put this formula in A2 and the data begins in A5 and runs trough row 500

I have attached a sample workbook with stripped down data.

I appreciate whatever assistance can be provided.

Greg :scratch:
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Count Distinct with multiple criteria excel 2016 Pro Plus

Post by HansV »

As an array formula confirmed with Ctrl+Shift+Enter:

=SUM(IF((A5:A163<>"NA")*(B5:B163="N"),1/COUNTIFS(A5:A163,A5:A163,A5:A163,"<>NA",B5:B163,"N")))

(This is based on your sample workbook. Change the ranges as needed)
Best wishes,
Hans

gvanhook
StarLounger
Posts: 67
Joined: 22 Feb 2010, 20:15
Location: Indiana

Re: Count Distinct with multiple criteria excel 2016 Pro Plus

Post by gvanhook »

Thanks Hans,
How would I adapt the formula to ignore blanks in column A? Since the file grows daily, I would like to set the range to a larger value and not have to update it everyday. for example using a range of rows 5 to 500.

Greg

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Count Distinct with multiple criteria excel 2016 Pro Plus

Post by HansV »

Simples:

=SUM(IF((A5:A1000<>"NA")*(B5:B1000="N"),1/COUNTIFS(A5:A1000,A5:A1000,A5:A1000,"<>NA",B5:B1000,"N")))

Don't forget to use Ctrl+Shift+Enter after editing the formula.
Best wishes,
Hans

gvanhook
StarLounger
Posts: 67
Joined: 22 Feb 2010, 20:15
Location: Indiana

Re: Count Distinct with multiple criteria excel 2016 Pro Plus

Post by gvanhook »

:brickwall: AHHHH I tried that same formula and wondered why it did not work. I missed updating one of the ranges.

Thanks You as always!

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Count Distinct with multiple criteria excel 2016 Pro Plus

Post by jstevens »

Greg,

Have you considered converting your data into an Excel table? Since the data set will grow daily you would not have to worry about changing the formula due to more rows being added. Simply add data to the table.

The attached file reflects an Excel table with a modification to Han's formula referencing a table named "MyTable".
Distinct_Count_Mutiple_Criteria.xlsx
You do not have the required permissions to view the files attached to this post.
Regards,
John

Nabeel
2StarLounger
Posts: 170
Joined: 26 Jan 2017, 07:24

Re: Count Distinct with multiple criteria excel 2016 Pro Plus

Post by Nabeel »

Here is another approach!

=COUNT(IF(MATCH(A5:A163,--(A5:A163)*(B5:B163="N"),0)=ROW(A5:A163)-4,ROW(A5:A163)-4,""))

Or

=COUNT(UNIQUE(FILTER(A5:B163,B5:B163="N")))

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Count Distinct with multiple criteria excel 2016 Pro Plus

Post by StuartR »

Nabeel wrote:
12 Sep 2020, 09:28
=COUNT(UNIQUE(FILTER(A5:B163,B5:B163="N")))
That is very elegant, for anyone that has the latest version of Excel
StuartR