Unique countifs

[Umesh Chahar]

Hi all,

Can anybody help me how to use UNIQUE function (introduced in office 365) to use with countifs ( multiple criteria)

 

[herofox]

upload small excel file .... Guessing can't be worked out !!!!!

 

[GraH - Guido]

Better use the combo with filter and wrap inside a count or counta.

 

[Umesh Chahar]

Please find the attached Sample Data file ,in the requirement sheet there are 4 drop downs in which value can be changed and i want unique count of customer code basis the criteria with sales greater than zero

also refer the data sheet to look for data

 

[anishms]

=COUNTA(UNIQUE(FILTER(Customer_Code,(Region=A3)*(Year=B3)*(Quarter=C3)*(Product=D3)*(Sales>0))))

 

[Umesh Chahar]

upload small excel file .... Guessing can't be worked out !!!!!

Please find the attached Sample Data file ,in the requirement sheet there are 4 drop downs in which value can be changed and i want unique count of customer code basis the criteria with sales greater than zero

also refer the data sheet to look for data

 

[Umesh Chahar]

=COUNTA(UNIQUE(FILTER(Customer_Code,(Region=A3)*(Year=B3)*(Quarter=C3)*(Product=D3)*(Sales>0))))

The formula is not giving correct answers , its giving customer count more than what i am getting the correct numbers using the Pivot ,but pivot cannot be used there ,hence need some formula to get the answer.

thanks in advance

 

[anishms]

The formula is not giving correct answers , its giving customer count more than what i am getting the correct numbers using the Pivot ,but pivot cannot be used there ,hence need some formula to get the answer.

thanks in advance

You missed an open bracket

 

[Umesh Chahar]

can you pls help on this , i as not able to get. still correct answer is not coming

You missed an open bracket

 

[GraH - Guido]

Formula provided by anishms was correct.

=IFERROR(
     COUNTA(
         UNIQUE(
             FILTER(Data!A:A ,
                          (Data!C:C=Requirement!A3)*
                          (Data!E:E=Requirement!B3)*
                          (Data!D:D=Requirement!C3)*
                          (Data!F:F=Requirement!D3)
                        )
         )
     );
  "No matching data found")

Setting the same filters manually in your file, shows same result.


PS: Remember for future posts, when you say it's not working, it is a best practice (polite even) to highlight where things are off according to your rules. Simply saying it is wrong does not help.