Counting unique entries per mon - formula help

[DaveR]

I am trying to calculate how may unique enteries there are per month.

I currently callucalte the total with;

=SUMPRODUCT((MONTH('Front Sheet'!$H$3:$H$292)=4)*(YEAR('Front Sheet'!$H$3:$H$292)=2011))

The list would be contained in column B (i.e the number of unique entries).

 

[Aladin Akyurek]

I am trying to calculate how may unique enteries there are per month.

I currently callucalte the total with;



The list would be contained in column B (i.e the number of unique entries).

The formula you have counts the April 2011 dates. Are you requesting a formula to determine the number of different (distinct) dates in April 1011? If so:

Let A2 house 4 and B2 2011.

C2, control+shift+enter, not just enter...

=SUM(IF(FREQUENCY(
    IF('Front Sheet'!$H$3:$H$292<>"",
    IF(MONTH('Front Sheet'!$H$3:$H$292)=A2,
    IF(YEAR('Front Sheet'!$H$3:$H$292)=B2,
      MATCH('Front Sheet'!$H$3:$H$292,'Front Sheet'!$H$3:$H$292,0)))),
        ROW('Front Sheet'!$H$3:$H$292)-ROW('Front Sheet'!$H$3)+1),1))

In D2, you could enter a revised version of the SumProduct formula...

=SUMPRODUCT(
   --(MONTH('Front Sheet'!$H$3:$H$292)=A2),
   --(YEAR('Front Sheet'!$H$3:$H$292)=B2))

 

[DaveR]

Thanks for this.

I did realise after posting that I already had a way to do it contained in the sheet.

I have a column C with;

=SUM(COUNTIF($B$3:$B$309,$B$3:$B$309))

Which then gives me an easy option to calculate the unique entries with;

=SUMPRODUCT((MONTH('Front Sheet'!$H$3:$H$292)=4)*(YEAR('Front Sheet'!$H$3:$H$292)=2011)*(('Front Sheet'!$C$3:$C$292)=1))

I'm still trying to work out which is quicker (we are on a citrix server and speed is vital)

 

[Aladin Akyurek]

Thanks for this.
...
I'm still trying to work out which is quicker (we are on a citrix server and speed is vital)

You are welcome. Here is a useful link:

http://www.mrexcel.com/forum/showthread.php?t=292473