Counting Unique Days based on multiple criteria

[eribs4e]

I've scoured the newsgroups and web for some type of solution that
would lead me to solving my problem but I still haven't been able to
figure it out, soooo....it's time to post.

The following is what my sample data looks like:

2008-08-23 10:02
2008-08-23 10:42
2008-08-24 11:25
2008-09-23 12:39
2008-09-23 14:37
2008-09-24 9:53
2008-09-24 10:08
2008-10-24 10:17
2008-10-24 10:47
2009-10-24 11:00
2009-10-25 11:11
2009-11-24 12:26

My requirements are twofold. Firstly I need a count of total entries
for each year/month combination. For example:

2008-08 = 3
2008-09 = 4
2008-10 = 2
2009-10 = 2
2009-11 = 1

I've got this working with the following formula:

SUMPRODUCT(--(MONTH(A1:A12)=08),--(YEAR(A1:A12)=2008))

My second requirement dictates that I count the number of unique days
worked for each year/month combination. For example:

2008-08 = 2
2008-09 = 2
2008-10 = 1
2009-10 = 2
2009-11 = 1

This is where I'm stuck. I've managed to multiple formulas to count
the number of unique days in the whole range, but I haven't been able
to come up with a formula that counts the unique days based on the
month/year criteria. Anyone have any ideas?

Thanx in advance for the help.
eribs4e

 

[Bob Phillips]

=SUM(--(FREQUENCY(IF((MONTH(A1:A12)=8)*(YEAR(A1:A12)=2008),MATCH(A1:A12,A1:A12,0)),ROW(INDIRECT("1:"&ROWS(A1:A12))))>0))

as an array formula, so commit with Ctrl-Shift-Enter

 

[eribs4e]

=SUM(--(FREQUENCY(IF((MONTH(A1:A12)=8)*(YEAR(A1:A12)=2008),MATCH(A1:A12,A1:­A12,0)),ROW(INDIRECT("1:"&ROWS(A1:A12))))>0))

as an array formula, so commit with Ctrl-Shift-Enter

--
__________________________________
HTH

Bob














- Show quoted text -

Thanx for the reply Bob, but the formula is returning the total count
for each month/year combo...not the unique count. e.g the formula
shoud return 2 for 2008-08...it returns 3.

 

[Don Guillett]

For your first set you could use this instead for col D
=SUMPRODUCT((TEXT($A$2:$A$22,"yyyy-mm")=C2)*1)

then you could use this macro to get the other from your list in col C to
get col E. Or combine

Sub countbymonthandyear()
For Each c In Range("c2:c6")
mc = 0
For Each d In Range("a2:a22")
'MsgBox Month(c)
'MsgBox Year(c)
If d <> d.Offset(1) And Month(d) = Month(c) And Year(d) = Year(c) Then mc =
mc + 1
Next d
'MsgBox mc
c.Offset(, 2) = mc
Next c
End Sub

 

[eribs4e]

I've scoured the newsgroups and web for some type of solution that
would lead me to solving my problem but I still haven't been able to
figure it out, soooo....it's time to post.

The following is what my sample data looks like:

2008-08-23 10:02
2008-08-23 10:42
2008-08-24 11:25
2008-09-23 12:39
2008-09-23 14:37
2008-09-24 9:53
2008-09-24 10:08
2008-10-24 10:17
2008-10-24 10:47
2009-10-24 11:00
2009-10-25 11:11
2009-11-24 12:26

My requirements are twofold. Firstly I need a count of total entries
for each year/month combination. For example:

2008-08 = 3
2008-09 = 4
2008-10 = 2
2009-10 = 2
2009-11 = 1

I've got this working with the following formula:

SUMPRODUCT(--(MONTH(A1:A12)=08),--(YEAR(A1:A12)=2008))

My second requirement dictates that I count the number of unique days
worked for each year/month combination. For example:

2008-08 = 2
2008-09 = 2
2008-10 = 1
2009-10 = 2
2009-11 = 1

This is where I'm stuck. I've managed to multiple formulas to count
the number of unique days in the whole range, but I haven't been able
to come up with a formula that counts the unique days based on the
month/year criteria. Anyone have any ideas?

Thanx in advance for the help.
eribs4e

I realized I omitted one very important piece of criteria in my
original post. I need the unique "Days" of activity for each
month....ignoring the time. I apologize for the oversight.....Sorry
Bob!!

er

 

[eribs4e]

I realized I omitted one very important piece of criteria in my
original post. I need the unique "Days" of activity for each
month....ignoring the time. I apologize for the oversight.....Sorry
Bob!!

er- Hide quoted text -

- Show quoted text -

Thanx to everyone for the suggestions but using Bob's formula solution
as a starting point I was able to make the modifications necessary to
get the result I needed. Thanx a bunch Bob.