I've been struggling for some time with this. Is there any way to sum only unique values if you can only use data in table below and:
- Sum
- Average
- number of rows
- number of unique rows
Column 'total sales...' has duplicates and it appears as many times as number of weeks. I can't get rid of duplicate and wonder how to solve this problem.
Desired sum is 122521 + 111696 + 122507 = 356724. If number of rows per week is static I could just divide sum of 'total sales...' per number of week and it would work. As you can see last week has 6 rows, so it won't work.
What I realized is that if I take average of number of rows (first column - in this case average of 4, 4 and 6 equals 4.666666667) it would work. Sum of all 'total sales...' divided by average of number of rows would give me pretty accurate result (1671910 / 4666666667). Is this possible to do it with only these 4 functions?
Thanks!