Combining Unique Identifier and Date Range Columns to display Unique Value Total

[jpapex]

Hello,


I have a really complicated issue. I am looking to count the number of unique phone numbers in a date range, for a unique inbound 800 line. So one column Sheet2!O:O contains the phone numbers and another column Sheet2!K:K contains the date ranges, and Sheet2!I:I contains the unique inbound 800 line. I am looking to display this total on another page using the Sheet 2 references. Any idea how I can do this? I already figured out how to total by date ranges and unique 800 line but this is a little more complicated with the unique values for unique phone numbers.


So it goes, "For this unique inbound 800 line" Sheet2!I:I, "Between these date ranges Sheet2!K:K, "I have this many unique inbound phone numbers/calls" Sheet2!O:O.


Thanks

 

[jpapex]

An example of the function I used to get the Total inbound 800 line calls in a date range is:


=COUNTIFS(Sheet2!I:I,"(866)409-1365",Sheet2!K:K,">=3/1/2012",Sheet2!K:K,"<=3/31/2012")


I need to add into this to count only unique values in the field Sheet2!O:O


If that helps.


Thanks!

 

[SirJB7]

Hi, jpapex!

Could you use helper columns?

Regards!

 

[NARAYANK991]

Hi ,


Try this array formula , entered using CTRL SHIFT ENTER :


=SUM(IF((Col_I=Phone_Number)*(Col_K>=From_Date)*(Col_K<=To_Date),1/COUNTIFS(Col_I,Phone_Number,Col_K,">="&From_Date,Col_K,"<="&To_Date,Col_O,Col_O)))


where Col_I , Col_K , Col_O , Phone_Number , From_Date and To_Date are all named ranges.


Copied from : http://www.get-digital-help.com/2011/07/12/count-unique-distinct-values-that-meet-multiple-criteria-in-excel/


Narayan

 

[jpapex]

Thanks SirJB7,


I will have to look up helper columns and how to identify unique values. The hard part for me is incorporating the date ranges that I need to count the unique values for.


Thanks Narayank991,


I have used the above formula you provided and came up with:


{=SUM(IF((Sheet2!I:I="(866)409-2388")*(Sheet2!K:K>=3/1/2012)*(Sheet2!K:K<=3/31/2012),1/COUNTIFS(Sheet2!I:I,"(866)409-2388",Sheet2!K:K,">="&3/1/2012,Sheet2!K:K,"<="&3/31/2012,Sheet2!O:O,Sheet2!O:O)))}


It comes up with 0 as the total. I am not sure what I am doing wrong here but this should be counting quite a few values.


Do you see anything that is off?


Thanks so much!

 

[NARAYANK991]

Hi ,


You need to concatenate correctly :


=SUM(IF((Col_I="(866)409-2388")*(Col_K>=DATEVALUE("3/1/2012"))*(Col_K<=DATEVALUE("3/31/2012")),1/COUNTIFS(Col_I,"(866)409-2388",Col_K,">="&DATEVALUE("3/1/2012"),Col_K,"<="&DATEVALUE("3/31/2012"),Col_O,Col_O)))


Narayan

 

[jpapex]

Hi Narayan,


I am still getting a total of 0 with:


{=SUM(IF((I:I="(866)409-2388")*(K:K>=DATEVALUE("3/1/2012"))*(K:K<=DATEVALUE("3/31/2012")),1/COUNTIFS(I:I,"(866)409-2388",K:K,">="&DATEVALUE("3/1/2012"),K:K,"<="&DATEVALUE("3/31/2012"),O:O,O:O)))}


I really appreciate your help on this!

 

[NARAYANK991]

Hi ,


I have tried it out and it works.


When I wrote about Col_I , Col_K and Col_O as named ranges , I meant that they were ranges with defined limits ; probably using I:I , K:K and O:O is the problem ; try replacing column references by proper range references.


Narayan

 

[jpapex]

Thanks Narayan!


That works great!! My sincerest appreciation!