How to Count Unique Dates based on Criteria

[sillerdu]

Hi,

I have been unable to find an answer to this one. Here is my question, I would like to count the number of unique dates in a sheet based on a criteria. For the example below, I would like to count the number of unique dates for only item F102840.

item	due_date
F102601	4/13/2016
F102650XL	11/15/2014
F102654XL	7/20/2014
F102657XL	10/27/2014
F102658XL	10/2/2014
F102834	10/26/2015
F102840	6/16/2014
F102840	6/24/2014

<colgroup><col><col></colgroup><tbody>
</tbody>

Thanks!!

 

[Aladin Akyurek]

item	due_date			F102840
F102601	4/13/2016			2
F102650XL	11/15/2014
F102654XL	7/20/2014
F102657XL	10/27/2014
F102658XL	10/2/2014
F102834	10/26/2015
F102840	6/16/2014
F102840	6/24/2014

<tbody>
</tbody>

In E2 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$9=E1,$B$2:$B$9),$B$2:$B$9),1))

 

[sillerdu]

Worked like a charm!!! Thank you Aladin!

 

[Aladin Akyurek]

Worked like a charm!!! Thank you Aladin!

You are welcome.

 

[TORPIDO]

item	due_date			F102840
F102601	4/13/2016			2
F102650XL	11/15/2014
F102654XL	7/20/2014
F102657XL	10/27/2014
F102658XL	10/2/2014
F102834	10/26/2015
F102840	6/16/2014
F102840	6/24/2014

<tbody>
</tbody>

In E2 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$9=E1,$B$2:$B$9),$B$2:$B$9),1))

Hi,

This seems to not work for me. Any idea?

 

[AhoyNC]

You need to give us more info. What does "seems not to work" mean - wrong answer, error msg.??
What version of Excel are you using?
The above is an array formula, did you enter the formula with CTRL-SHIFT-ENTER?
Post a small sample of your data (using XL2BB if you can). Also, expected result.
In the example above the dates are numeric, if your data is text the formula will need to be changed.

 

[TORPIDO]

Thanks, seemed if column has blank cells or #n/a the formula would not work,did some clean up and it worked.

 

[syedanis]

I want to know, how to modify the above formula for multiple criteria. like instead of one value in E2, if we have a list of items for which we need to check how many occurrences for each item.

 

[AhoyNC]

Need to post a small sample of your data with the results you expect. Are your list of items going to be in separate cells or the same cell?

 

[Aladin Akyurek]

Thanks, seemed if column has blank cells or #n/a the formula would not work,did some clean up and it worked.

What did you exactly clean up ?
What is the final formula you have applied ?

One way you can modify the formula posted for sillerdu is:

=SUM(IF(FREQUENCY(IF(ISTEXT($A$2:$A15),IF($A$2:$A$15=E1,IF(ISNUMBER($B$2:$B$15),$B$2:$B$15))),IF(ISNUMBER($B$2:$B$15),$B$2:$B$15)),1))

which also must be confirmed with control+shift+enter.