• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find unique on basis of with multiple criteria

M

MohitMathur1234

Member
Hi All,

I want to unique count of Emp ID on basis of Year and qtr which will be selected from drop down, So If I select year and qtr from drop down so I got to know unique count of employee.

Easy reference please find attachment.
 

Attachments

  • Test1.xlsx
    68 KB · Views: 18
Hi,

Check this formula,
=SUM(--(FREQUENCY((J2:J4982=B2)*(K2:K4982=E2)*L2:L4982,L2:L4982)>0))

David
 

Hi David !

With the advanced filter way as any Excel beginner should try
the result is 6 and with your formula it's 7 ! …
​
 
Hi ,

Try either of the following array formulae , to be entered using CTRL SHIFT ENTER :

=SUM(IF($J$2:$J$4982 = $B$2, IF($K$2:$K$4982 = $E$2, 1/COUNTIFS($J$2:$J$4982, $B$2, $K$2:$K$4982, $E$2, $L$2:$L$4982, $L$2:$L$4982))))

=SUM(--(FREQUENCY(IF((J2:J4982=B2)*(K2:K4982=E2),L2:L4982),IF((J2:J4982=B2)*(K2:K4982=E2),L2:L4982))>0))

Narayan
 

As my Excel 2003 is a local version, David's formula was entered
via ActiveCell.FormulaArray and result is 7 …

I will test later on another computer with a 2007 or a 2010 version,
to be continued …
​
 
Another method using PowerQuery, just for reference.

Convert range into table.

Name Cell D2 as crit1
Name Cell G2 as crit2

Load from Table to PowerQuery. Copy and paste following M in Advanced Editor.
Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    criteria1 = Excel.CurrentWorkbook(){[Name="crit1"]}[Content]{0}[Column1],
    criteria2 = Excel.CurrentWorkbook(){[Name="crit2"]}[Content]{0}[Column1],

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Qtr", type text}, {"Employee ID", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Qtr] = criteria2)),
    #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows", each ([Year] = criteria1)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Year", "Qtr"}, {{"Count", each Table.RowCount(Table.Distinct(_)), type number}})
in
    #"Grouped Rows"

Load table to sheet.

Note: If data does not exist in table (such as 2015 Q1) , the table will not update with query result.

Lately, I've been using PowerQuery more and more as it does not require data to be in Excel sheet (can be done directly from database query) and can use dynamic parameters.
 

Attachments

  • Test1.xlsx
    80.2 KB · Views: 1
Thanks everyone,
But this formula is not working for me because in this formula range is fixed to 4982, but I want formula where range will increase in every qtr, I have used Offset but this formula is showing error.. Please help..

And also suggest Why I am not able to use offset for qtr column, Year and Emp offset is working fine. Exp :- for Emp -=OFFSET(Sheet1!$L$2,0,0,COUNT(Sheet1!$L:$L),1)
 
Hi Mohit,

You can simply modify range part with INDIRECT

=SUM(--(FREQUENCY(IF((J2:J4982=B2)*(K2:K4982=E2),L2:L4982),L2:L4982)>0))

Same for K,L,J like

L2:L4982 change with INDIRECT("L2:L"&COUNTA(L2:L71000))



MohitMathur1234 said:
Thanks everyone,
But this formula is not working for me because in this formula range is fixed to 4982, but I want formula where range will increase in every qtr, I have used Offset but this formula is showing error.. Please help..

And also suggest Why I am not able to use offset for qtr column, Year and Emp offset is working fine. Exp :- for Emp -=OFFSET(Sheet1!$L$2,0,0,COUNT(Sheet1!$L:$L),1)
Click to expand...
 
Hi ,

You can extend the range, for example to 10000 lines,

The function FREQUENCY is very fast, there is no problem of large ranges.

=SUM(--(FREQUENCY(IF((J2:J10001=B2)*(K2:K10001=E2),L2:L10001),L2:L10001)>0))

entered CTRL SHIFT ENTER.

Just do not use volatile functions.

David
 
You must log in or register to reply here.
Back
Top