+ Reply to Thread
Results 1 to 8 of 8

Formula needed for calculating one of three activity types once... if same date/time

  1. #1
    Registered User
    Join Date
    05-04-2015
    Location
    NH
    MS-Off Ver
    Office 365
    Posts
    27

    Formula needed for calculating one of three activity types once... if same date/time

    I am looking for a formula to calculate one of three activity types once, if all three occur on the same date and time. Is this possible to do through an Excel formula? The values would likely be grouped together... either two of three, or all three. I have attached an example (rows 3-5).

    Combinations can be:

    ACTIVITY_CREATE_QUESTION
    ACTIVITY_CREATE_MESSAGE
    ACTIVITY_CREATE_THREAD

    ACTIVITY_CREATE_QUESTION
    ACTIVITY_CREATE_MESSAGE

    ACTIVITY_CREATE_QUESTION
    ACTIVITY_CREATE_THREAD

    ACTIVITY_CREATE_MESSAGE
    ACTIVITY_CREATE_THREAD

    ACTIVITY_CREATE_MESSAGE
    ACTIVITY_CREATE_QUESTION

    ACTIVITY_CREATE_THREAD
    ACTIVITY_CREATE_QUESTION

    ACTIVITY_CREATE_THREAD
    ACTIVITY_CREATE_MESSAGE

    There may be up to 200,000 records, and not all activity types may exist but it is 95%+ likely all three will be used.

    If one of the above combinations are used with the same date/time, I would just like to count that as one occurrence.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Formula needed for calculating one of three activity types once... if same date/time

    With a pivot table.

    See the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    05-04-2015
    Location
    NH
    MS-Off Ver
    Office 365
    Posts
    27

    Re: Formula needed for calculating one of three activity types once... if same date/time

    Thank you. This would be a good solution, but I meant to clarify a formula would be needed because this is just one calculation of many others (~20-30) in an Excel sheet. Is it possible to do this in a formula?

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,928

    Re: Formula needed for calculating one of three activity types once... if same date/time

    =SUMPRODUCT((MMULT((COUNTIFS(A2:A7,{"ACTIVITY_CREATE_QUESTION","ACTIVITY_CREATE_MESSAGE","ACTIVITY_CREATE_THREAD"},C2:C7,C2:C7)>0)*1,{1;1;1})=3)*1)/3
    try this
    if it is not worked what is your expected result for attached sample file
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Formula needed for calculating one of three activity types once... if same date/time

    Maybe the other questions can also be solved with a pivot table.

  6. #6
    Registered User
    Join Date
    05-04-2015
    Location
    NH
    MS-Off Ver
    Office 365
    Posts
    27

    Re: Formula needed for calculating one of three activity types once... if same date/time

    Thanks Siva. In the actual data set, there are multiple dates. How can I consider the date column in the formula as well? It would need to first look at date, then time.

  7. #7
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,928

    Re: Formula needed for calculating one of three activity types once... if same date/time

    =sumproduct((mmult((countifs(a2:a7,{"activity_create_question","activity_create_message","activity_create_thread"},b2:b7,b2:b7,c2:c7,c2:c7)>0)*1,{1;1;1})=3)*1)/3
    Please Login or Register  to view this content.

  8. #8
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Formula needed for calculating one of three activity types once... if same date/time

    Are the three actions the only actions in the full dataset? If yes, all you have to compare is date/time. Assuming in chronological order as in the example, D1 is automatically 1. Then in D2, the array formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and confirm entry with Ctrl+Shift+Enter, then copy down.

    ...or are you looking for a total count in one cell?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Activity Time Tracker to capture activity selected from dropdown list
    By dillibabu in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-24-2013, 01:10 PM
  2. Replies: 2
    Last Post: 06-26-2013, 01:31 AM
  3. Need help with calculating total time of a particular event/activity
    By shailesh in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 01-14-2012, 05:55 AM
  4. Date and Time Stamp for Last Activity in Range
    By XLevel in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-19-2011, 07:34 AM
  5. Replies: 0
    Last Post: 09-03-2010, 05:36 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1