Activity/Day count

[aylar]

​Hi,

I'm not sure where to start with this right now, a little in over my head. I'm trying to create a formula that counts the number of days a month an activity is performed (A) by certain users (B) based off of the transactions (C). The transacation dates also need to be redefined, as the shifts occur overnight. This is what a sample of the data looks like:

User	Activity	Transaction time	Formula output - # of Activity days per period
A	1	11/28/2015 7:11	1
B	1	11/29/2015 8:11	1
C	1	11/29/2015 9:11	1
C	2	11/29/2015 10:11	1
A	1	11/29/2015 11:11	2
C	2	11/29/2015 12:11	1
A	3	11/30/2015 9:00	1
B	2	11/30/2015 10:01	1
A	2	11/30/2015 10:30	1
C	3	11/30/2015 11:30	1
D	1	11/30/2015 12:30	1
A	1	12/1/2015 8:11	3
B	2	12/1/2015 9:03	2
B	2	12/1/2015 10:11	2

Any help would be appreciated

 

[elmer007]

Since this hasn't had an answer yet, let's see if we can't get things started. I thought I understood your goal, but the example table doesn't fit with what I thought. Should the entry on 11/29/2015 12:11 have a result of 2 instead of 1 (as shown)?

I tried the following setup; note the formula in D2 is copied down the column:

Excel 2010
	A	B	C	D
1	User	Activity	Transaction time	Formula output - # of Activity days per period
2	A	1	11/28/2015 7:11	1
3	B	1	11/29/2015 8:11	1
4	C	1	11/29/2015 9:11	1
5	C	2	11/29/2015 10:11	1
6	A	1	11/29/2015 11:11	2
7	C	2	11/29/2015 12:11	2
8	A	3	11/30/2015 9:00	1
9	B	2	11/30/2015 10:01	1
10	A	2	11/30/2015 10:30	1
11	C	3	11/30/2015 11:30	1
12	D	1	11/30/2015 12:30	1
13	A	1	12/1/2015 8:11	3
14	B	2	12/1/2015 9:03	2
15	B	2	12/1/2015 10:11	3
Sheet1

Cell Formulas
Range	Formula
D2	=COUNTIFS(A$1:A1,A2,B$1:B1,B2)+1

Do the counts need to reset when the transaction time enters a new month?

 

[elmer007]

I think if you want the count to reset for a new month, D2 should be: =COUNTIFS(A$1:A1,A2,B$1:B1,B2,C$1:C1,">="&(MONTH(C2)&"/1/"&YEAR(C2)),C$1:C1,"<="&EOMONTH(C2,0))+1
and copy down.

This may not work correctly- let me know if it doesn't.

 

[aylar]

Thanks Elmer,

There wasn't a mistake in the data set. If the activity is performed multiple times on the same day, I would like it to only be counted once. I'm not sure what the logic would look like for that exception, but thanks for steering me in the right direction.

 

[aylar]

Bump -

I haven't been able to solve this problem still. I don't know why this didn't work. I made a helper column in E that rounded all the transaction times down to midnight of that day, and then changed the formula to
=COUNTIFS(A$1:A1,A2,B$1:B1,B2,E$1:E1,E2)+1

Again, activities counted on the same day to not be counted.
Any help appreciated,
Thanks.