Is it possible for me to use CountIf or SumIf in Excel for this use case?
I would like to count the number of occurrences of "ACTIVITY_CREATE_USER" (in my Action column) for the last 30 days.
I have an "Action" column and a "ActivityTime.Date" column.
Sample data sheet attached:
SampleValues.xlsx
Hi, welcome to the forum
Try this...
=COUNTIFS($A$2:$A$28,"ACTIVITY_CREATE_USER",$B$2:$B$28,">="&TODAY()-30,$B$2:$B$28,"<="&TODAY())
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Try this
=COUNTIFS(B2:B28,">="&MAX(B2:B28)-30,B2:B28,"<="&MAX(B2:B28),A2:A28,"ACTIVITY_CREATE_USER")
Last edited by AlKey; 05-04-2015 at 06:51 PM.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Good catch. I was thinking within the last 30 days from today - which is not necessarily the caseOriginally Posted by AlKey
Thanks Ford. This works great. How could I modify this formula to apply to two use cases (below)?
1. Returning visitors (at least 1 login from previous 30 days). I imagine I would just need to modify your formula to check against a new "username" column and limit records to one login only.
2. New visitors (at least one login from someone that has not signed in before (all time)). Also checking against a third "username" column.
Another possibility is:
Formula:
Please Login or Register to view this content.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks