• 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.

Count unique from 3 columns ignoring zero

G

gauravkrgautam

Member
Hi All,

i need a formula to Daywise unique count of code. but if there is any zero it should not be include in count. presently i draw a pivot in tabular format and then i count codes one by one for each date. a sample file is attached. thanks in advance for your assistance.
 

Attachments

  • sample.xlsx
    53.2 KB · Views: 10
dear freind , i have created a pivot to match your result and found that your answer is perhaps wrong. Therefore please send the file with drawn pivot table so that i could understand what is your requirement and how you have got this result from pivot.
 
Thanks Ram, please find the attached sample file with pivot where i want date wise unique count excluding zero. please take care of below points while making pivot
1-exclude zero in report filter
2-date & code in row label
3-pivot in tabular format so that we able to count by function or by selection manuallyo_O
 

Attachments

  • sample WITH PIVOT.xlsx
    81.5 KB · Views: 5
This array formula works and produces the same result as you have calculated.

=SUMPRODUCT(IFERROR((1/COUNTIFS($A$2:$A$1399,F9,$C$2:$C$1399,"<>0",$B$2:$B$1399,$B$2:$B$1399)),0)*($A$2:$A$1399=F9)*($C$2:$C$1399<>0))

Make sure you press CTRL+Shift+Enter after typing it.

I am sure this formula can be improved. but I cant immediately think of a way to do it.
 
Yipeee.... double bounus for me
first my prblem is resolved
second solved by chandoo....

good morning chandoo...
array is working f9 .....:DD
 
i had pasted your formula in front of 2 Dec 13 then result are shown 58 not 35. please check again . Perhaps answer is coming from Sum of values not from count of unique codes.
 
Ram Mher said:
Gd Morning Chandoo
Can we count it through Pivot. Please provide the solution.
Click to expand...

You can if you are using Excel 2013. Follow these steps.

1. convert original data to a table (press CTRL+T)
2. Create a pivot table by going to insert ribbon.
3. During creation, check the box to add pivot to data model.

screen-0292.png

4. Add date to row labels, code to value field
5. Add Sum to report filter and choose to display report for all values except 0
6. Right click on code value field, choose value field settings and summarize values by distinct count

screen-0293.png

See attached file.

NOTE: The Excel workbook works only Excel 2013.

PS: If you have Excel 2010, you can use PowerPivot to do this too.
 

Attachments

  • unique-count-gaurav.xlsx
    169.3 KB · Views: 2
Ram Mher said:
i had pasted your formula in front of 2 Dec 13 then result are shown 58 not 35. please check again . Perhaps answer is coming from Sum of values not from count of unique codes.
Click to expand...

And CTRL+Shift+Enter my friend..
 
@gauravkrgautam ...

Another array formula approach. Enter with Ctrl+Shift+Enter

Code: 
=SUM(IF(FREQUENCY(IF($A$2:$A$1399=$F9,IF($C$2:$C$1399<>0,MATCH($B$2:$B$1399,$B$2:$B$1399&"",0))),ROW($B$2:$B$1399)-ROW($B$2)+1),1))

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