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

Counting Unique Values and Date Ranges Together

J

jpapex

New Member
Hello,


I am looking to get a count of unique calls (phone numbers) in a date range.


Sheet2!K:K is the Date Column

Sheet2!O:O is the Phone Number Column


I am trying to get all unique phone number values between a date range of 5/1/2013 and 5/14/2013.


Can anyone help?


If possible, I am trying to not use an array, but if not, an array will be okay.


Thanks,

John
 
jpapex: these posts might be of interest:

http://chandoo.org/forums/topic/formula-challenge-007-return-distinctduplicate-unique-values-as-an-array

http://chandoo.org/forums/topic/formula-challenge-007-return-distinctduplicate-unique-strings-as-an-array


I suggest you post a link to a sample workbook...much easier for people to answer your question then.
 
Hi Jeff,


I don't have a way to post my spreadsheet online.


Those articles don't seem to address the combination of calculations that I am trying to get.


I need to combine a sum of all records in a date range and reduce that sum by the amount of records holding a unique value in a phone number column. This would get me a sum of unique records in a date range.


I have successfully tried:


=SUM(IF((Sheet2!K1:K50000>=DATEVALUE("5/1/2013"))*(Sheet2!K1:K50000<=DATEVALUE("5/14/2013")),1/COUNTIFS(Sheet2!K1:K50000,">="&DATEVALUE("5/1/2013"),Sheet2!K1:K50000,"<="&DATEVALUE("5/14/2013"),Sheet2!O1:O50000,Sheet2!O1:O50000)))


I am looking for a way to do this that isn't an array, if possible.


Thanks,

John
 
I've seen some references to SUMPRODUCT as a non-array type of formula that might be used for this but I can't get it to work.
 
In terms of posting a sample spreadsheet, here's a link: http://chandoo.org/forums/topic/posting-a-sample-workbook


In terms of your requirements, have you considered a pivottable?
 
Hi John ,


I am not sure , but I think you are referring to SUMPRODUCT and COUNTIF , where a non-array formula is possible ; when you use COUNTIFS , you need to use an IF , and enter it as an array formula.


Check these links :


http://www.mrexcel.com/forum/excel-questions/70835-count-distinct-function.html


http://www.get-digital-help.com/2009/09/13/count-unique-distinct-records-by-date-in-excel/


Narayan
 
This array formula will return the count of distinct phone numbers that made calls between a startdate and enddate (inclusive):

=SUM((date>=StartDate)*(date<=EndDate)*(MATCH(Phone,Phone,0)=1+ROW(Phone)-ROW(INDEX(Phone,1))))

...where your dates are in a named range called 'date', and your phone numbers are in a namedrange called 'Phone' and your start and end dates are in named ranges called 'StartDate' and 'EndDate'.


You need to enter it with CTRL + SHIFT + ENTER


Or you could just make a pivot out of your data, filter on the dates, drag the phone numbers to 'Row Lables' field and simply count the number of rows.
 
Hi again, John. This workbook shows how to implement both a pivottable approach and a formula approach. https://www.dropbox.com/s/7qsw8e1r1vhoh37/Count%20distinct%2C%20unique%2C%20duplicate%20calls%20between%20two%20dates_20130718%20v2.xlsx
 
You must log in or register to reply here.
Back
Top