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

[ccarmichael]

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.

[oeldere]

With a pivot table.

See the attached file.

[ccarmichael]

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?

[samba_ravi]

=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

[oeldere]

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

[ccarmichael]

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.

[samba_ravi]

=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.

[jhren]

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:

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?