Forum Discussion
In Excel Extracting Commonality Across Mutiple Columns and Thousands of Cells of Data
I have 5 daily columns, each with the email addresses of thousands of people who completed a task on each day. I am trying to find a way to display how many people across the five columns/days completed the task on all of the five days. Help please.
3 Replies
If that's something like this
Power Query could work especially taking into account that's thousands of records. Query the range, unpivot all columns, group by with count on days as aggregation, filter on count 5.
JeffFowlerjfowler72 , say if the data is in columns A~E , then the required formula could be
{=SUM((A:A=B:B)*(A:A=C:C)*(A:A=D:D)*(A:A=E:E)*NOT(A:A=""))}See attached example file i created with dummy data and verify if your problem was understood correctly or not.
If not, then suggest you to share a sample data, highlighting the valid cases to be counted.
You will probably need something like =COUNTIF or =COUNTIFS.
If you could provide an example of your spreadsheet or a screenshot (make sure you blur out or modify the email address for confidentiality purposes) it would be easier to help you.
