Count unique days within month

hi,

i need for my analyses number of unique days within a month:

column B - dates (some days are missing, some days have 20 lines, some have 1 line)
column C - =Month(Bx)

I need to count number of days within given month.

to count all unique days, i use this:
=SUM(IF(FREQUENCY(INPUT!B15:B2000;INPUT!B15:B2000)>0;1))

how do I add the condition IF Cx=8 ?

thanks,
Jirka

Hello Jirka, will all the dates be within a single year or do you want to look for different dates within August in different years? If it's the former then try this formula

=SUM(IF(FREQUENCY(IF(MONTH(Input!B15:B2000)=8;DAY(Input!B15:B2000));ROW(INDIRECT("1:31")));1))

note that column C isn't required....

When you have data for more than one year will you require a separate count for August 2010 unique dates and August 2011 unique dates? If so you can amend like this to include the year

=SUM(IF(FREQUENCY(IF(MONTH(Input!B15:B2000)=8;IF(YEAR(Input!B15:B2000)=2010;DAY(Input!B15:B2000))); ROW(INDIRECT("1:31")));1))

or you could simplify if you do use column C.....but with MONTH and YEAR, e.g. in C15 use this formula

=MONTH(B15)&"-"&YEAR(B15)

then formula for unique count can be

=SUM(IF(FREQUENCY(IF(Input!C15:C2000)="8-2010";DAY(Input!B15:B2000));ROW(INDIRECT("1:31")));1))

Originally Posted by jirib

oops, this formula doesnt work, I think FREQUENCY is missing an argument, but im not sure how to fix it

My version of excel requires commas instead of semi-colons but with that adjustment it works for me, it's an array formula, did you confirm with CTRL+SHIFT+ENTER?

This part

IF(MONTH(Input!B15:B2000)=8,DAY(Input!B15:B2000))

provides the "data array" argument for FREQUENCY and then this part

ROW(INDIRECT("1:31"))

provides the "bins"

....another way to approach this, which gives you a much more efficient formula, is simply to search for each date from the month in question within your range of dates, and count the number that exist there, i.e.

=SUMPRODUCT(ISNUMBER(MATCH(ROW(INDIRECT(A1&":"&EOMONTH(A1,0)));input!B15:B2000;0))+0)

Where A1 contains the 1st of the month in question, e.g. 1-Aug-2010

...just for completeness, one more approach you can use in Excel 2010. If you use your range of dates as a holiday range in a NETWORKDAYS.INTL function, customised to count all days as working days, then that gives you the number of days within that month that don't appear......so just subtracting that from the total number of days in the month will give you the correct answer, i.e.

=DAY(EOMONTH(A1,0))-NETWORKDAYS.INTL(A1,EOMONTH(A1,0),"0000000",input!B15:B2000)

where A1 contains the 1st of the month of interest, as before