By doing a csv export of your engagement results (go to the engagement overview on your platform and click "csv export") you can do this calculation manually in excel. Here's the steps

1. Download the CSV on your engagement overview page as admin.

2. Open it in excel (open it using the data import wizard if it looks weird) and split the final column into multiple columns using the text to column function (select comma as separator).

3. Select all of those newly created columns (including the original one) and paste them into a new tab (call that tab 'Calc', to make it easier to paste the formulas later). Select all of your pasted fields and click the 'Remove Duplicates' button.

4. Now copy paste all of the left over columns into one column. You can copy paste the empty cells as well. And then click the 'Remove Duplicates' button again.

5. Now you're left with a single list of all of the different teams you have. Add 2 rows on the top and enter these values at the top (replace the dates by the period you're investigating):

6. Enter this formula in cell B3: (It looks daunting but it's actually the same formula over and over again to take into account all the different team columns we've created in step 2. FYI this formula goes up to 7 columns. If you have more you're going to have to adapt the formula)

=(SUMIFS(intuo_export!L:L,intuo_export!O:O,A3,intuo_export!A:A,"<"&Calc!B$1,intuo_export!A:A,">"&Calc!B$2)+SUMIFS(intuo_export!L:L,intuo_export!P:P,A3,intuo_export!A:A,"<"&Calc!B$1,intuo_export!A:A,">"&Calc!B$2)+SUMIFS(intuo_export!L:L,intuo_export!Q:Q,A3,intuo_export!A:A,"<"&Calc!B$1,intuo_export!A:A,">"&Calc!B$2)+SUMIFS(intuo_export!L:L,intuo_export!R:R,A3,intuo_export!A:A,"<"&Calc!B$1,intuo_export!A:A,">"&Calc!B$2)+SUMIFS(intuo_export!S:S,intuo_export!R:R,A3,intuo_export!A:A,"<"&Calc!B$1,intuo_export!A:A,">"&Calc!B$2)+SUMIFS(intuo_export!T:T,intuo_export!R:R,A3,intuo_export!A:A,"<"&Calc!B$1,intuo_export!A:A,">"&Calc!B$2)++SUMIFS(intuo_export!U:U,intuo_export!R:R,A3,intuo_export!A:A,"<"&Calc!B$1,intuo_export!A:A,">"&Calc!B$2))/(SUMIFS(intuo_export!M:M,intuo_export!L:L,"<>",intuo_export!O:O,A3,intuo_export!A:A,"<"&Calc!B$1,intuo_export!A:A,">"&Calc!B$2)+SUMIFS(intuo_export!M:M,intuo_export!L:L,"<>",intuo_export!P:P,A3,intuo_export!A:A,"<"&Calc!B$1,intuo_export!A:A,">"&Calc!B$2)+SUMIFS(intuo_export!M:M,intuo_export!L:L,"<>",intuo_export!Q:Q,A3,intuo_export!A:A,"<"&Calc!B$1,intuo_export!A:A,">"&Calc!B$2)+SUMIFS(intuo_export!M:M,intuo_export!L:L,"<>",intuo_export!R:R,A3,intuo_export!A:A,"<"&Calc!B$1,intuo_export!A:A,">"&Calc!B$2)+SUMIFS(intuo_export!S:S,intuo_export!R:R,A3,intuo_export!A:A,"<"&Calc!B$1,intuo_export!A:A,">"&Calc!B$2)+SUMIFS(intuo_export!T:T,intuo_export!R:R,A3,intuo_export!A:A,"<"&Calc!B$1,intuo_export!A:A,">"&Calc!B$2)++SUMIFS(intuo_export!U:U,intuo_export!R:R,A3,intuo_export!A:A,"<"&Calc!B$1,intuo_export!A:A,">"&Calc!B$2))

7. Add a total field wherever you want with this formula:

=SUM(intuo_export!L:L)/SUMIFS(intuo_export!M:M,intuo_export!L:L,"<>")


8. Your Calc sheet should look like this:

Find an example excel here: https://drive.google.com/a/intuo.io/file/d/1firtljke4zofbITo7WS9fEnSxS0yFSab/view?usp=sharing 

Did this answer your question?