Need formula to Count the Date without duplicates

Hi All,

Need help in counting the MTD count of date without the duplicates.

My formula so far is, =COUNTIFS($A$5:$A$13,">="&($B$1)-(DAY($B$1)-1),$A$5:$A$13,"<="&($B$1),$C$5:$C$13,B2)

the problem is it counts all the duplicate Dates.

I attached here a sample excel file. hope it helps.


Thanks!


>>> Edit remarks: Kindly refer to the new attachment file in Post #10

So basically you need to count the Unique Dates from Month Start till Today?

Try this -

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Try this...

B1 = today's date

Enter this formula in C1. This will return the 1st of the month date.

=B1-DAY(B1)+1

Format as Date

This array formula** entered in F5:

=SUM(IF(FREQUENCY(IF((A5:A13>=C1)*(A5:A13<=B1),A5:A13),A5:A13),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Try this...

Data Range

	A	B	C
1	Date :	7/27/2015	7/1/2015
2
3
4	Activity:	Activity 1	Activity 2
5	Monthly Count of Date	3	3

Enter this formula in C1:

=B1-DAY(B1)+1

This array formula** entered in B5 and copied across to C5:

=SUM(IF(FREQUENCY(IF((Sheet2!$A2:$A19>=$C1)*(Sheet2!$A2:$A19<=$B1)*(Sheet2!$C2:$C19=B4),Sheet2!$A2:$A19),Sheet2!$A2:$A19),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.