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
Count Distinct with multiple criteria excel 2016 Pro Plus
-
- StarLounger
- Posts: 67
- Joined: 22 Feb 2010, 20:15
- Location: Indiana
Count Distinct with multiple criteria excel 2016 Pro Plus
You do not have the required permissions to view the files attached to this post.
-
- 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
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)
=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
Hans
-
- StarLounger
- Posts: 67
- Joined: 22 Feb 2010, 20:15
- Location: Indiana
Re: Count Distinct with multiple criteria excel 2016 Pro Plus
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
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
-
- 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
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.
=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
Hans
-
- StarLounger
- Posts: 67
- Joined: 22 Feb 2010, 20:15
- Location: Indiana
Re: Count Distinct with multiple criteria excel 2016 Pro Plus
AHHHH I tried that same formula and wondered why it did not work. I missed updating one of the ranges.
Thanks You as always!
Thanks You as always!
-
- GoldLounger
- Posts: 2618
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: Count Distinct with multiple criteria excel 2016 Pro Plus
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".
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".
You do not have the required permissions to view the files attached to this post.
Regards,
John
John
-
- 2StarLounger
- Posts: 170
- Joined: 26 Jan 2017, 07:24
Re: Count Distinct with multiple criteria excel 2016 Pro Plus
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")))
=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")))
-
- Administrator
- Posts: 12577
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Count Distinct with multiple criteria excel 2016 Pro Plus
That is very elegant, for anyone that has the latest version of Excel
StuartR