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

1. ## 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_QUESTION
ACTIVITY_CREATE_MESSAGE

ACTIVITY_CREATE_QUESTION

ACTIVITY_CREATE_MESSAGE

ACTIVITY_CREATE_MESSAGE
ACTIVITY_CREATE_QUESTION

ACTIVITY_CREATE_QUESTION

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.

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

With a pivot table.

See the attached file.

3. ## 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. ## Re: Formula needed for calculating one of three activity types once... if same date/time

try this
if it is not worked what is your expected result for attached sample file

5. ## 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. ## 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. ## Re: Formula needed for calculating one of three activity types once... if same date/time

``Please Login or Register  to view this content.``

8. ## 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:
`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?

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

#### 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