I launched a week ago into my biggest google sheet formula and I am reaching the limits of what I can do. I usually always find the answers to my questions by searching a little, but not this time.
I have exported a list of projects since the start of the company in 2017, there are more than 9000 lines.
Inside there are lots of columns, but the ones that interest us are:
In another sheet I wanted to show some columns based on some criteria but based on unique values from customers.
With as column:
In addition to that, I have dedicated cells to choose dates, so that users of this table can sort by month and / or year (see screenshot).
I started by using the UNIQUE function in column A to output the customer list. I then made SUMPRODUCT in the other columns according to my criteria. But 6 columns x 600 rows of SUMPRODUCT, googlesheet takes 5 min to give me a result every time I change my dates ... So I looked around a bit and discovered the UNIQUE, FILTER and SUMIF mix.
To help me with that, I created columns at the end of my 1st table (see 1st screenshot) which will help me with my SUMIFS (SOMME.SI in French), there will only need to be the addition of each of the rows, with as criterion the UNIQUE of the 1st column and the dates. If I did that, it's because I couldn't do SUMPRODUCTs in ArrayFormula.
By trying a little I came up with a huge formula.
=sort(
{unique(filter('DATA OPPORTUNITY'!E2:E,'DATA OPPORTUNITY'!B2:B>=B1,'DATA OPPORTUNITY'!B2:B<=B2)),
ArrayFormula(SOMME.SI('DATA OPPORTUNITY'!E2:E,unique(filter('DATA OPPORTUNITY'!E2:E,'DATA OPPORTUNITY'!B2:B>=B1,'DATA OPPORTUNITY'!B2:B<=B2)),'DATA OPPORTUNITY'!W2:W)),
ArrayFormula(SOMME.SI('DATA OPPORTUNITY'!E2:E,unique(filter('DATA OPPORTUNITY'!E2:E,'DATA OPPORTUNITY'!B2:B>=B1,'DATA OPPORTUNITY'!B2:B<=B2)),'DATA OPPORTUNITY'!X2:X)),
ArrayFormula(SOMME.SI('DATA OPPORTUNITY'!E2:E,unique(filter('DATA OPPORTUNITY'!E2:E,'DATA OPPORTUNITY'!B2:B>=B1,'DATA OPPORTUNITY'!B2:B<=B2)),'DATA OPPORTUNITY'!Y2:Y)),
ArrayFormula(SOMME.SI('DATA OPPORTUNITY'!E2:E,unique(filter('DATA OPPORTUNITY'!E2:E,'DATA OPPORTUNITY'!B2:B>=B1,'DATA OPPORTUNITY'!B2:B<=B2)),'DATA OPPORTUNITY'!Z2:Z)),
SIERREUR(ArrayFormula(SOMME.SI('DATA OPPORTUNITY'!E2:E,unique(filter('DATA OPPORTUNITY'!E2:E,'DATA OPPORTUNITY'!B2:B>=B1,'DATA OPPORTUNITY'!B2:B<=B2)),'DATA OPPORTUNITY'!W2:W))/
ArrayFormula(SOMME.SI('DATA OPPORTUNITY'!E2:E,unique(filter('DATA OPPORTUNITY'!E2:E,'DATA OPPORTUNITY'!B2:B>=B1,'DATA OPPORTUNITY'!B2:B<=B2)),'DATA OPPORTUNITY'!X2:X)),""),
SIERREUR(ArrayFormula(SOMME.SI('DATA OPPORTUNITY'!E2:E,unique(filter('DATA OPPORTUNITY'!E2:E,'DATA OPPORTUNITY'!B2:B>=B1,'DATA OPPORTUNITY'!B2:B<=B2)),'DATA OPPORTUNITY'!X2:X))/
(ArrayFormula(SOMME.SI('DATA OPPORTUNITY'!E2:E,unique(filter('DATA OPPORTUNITY'!E2:E,'DATA OPPORTUNITY'!B2:B>=B1,'DATA OPPORTUNITY'!B2:B<=B2)),'DATA OPPORTUNITY'!X2:X))+
ArrayFormula(SOMME.SI('DATA OPPORTUNITY'!E2:E,unique(filter('DATA OPPORTUNITY'!E2:E,'DATA OPPORTUNITY'!B2:B>=B1,'DATA OPPORTUNITY'!B2:B<=B2)),'DATA OPPORTUNITY'!Y2:Y))+
ArrayFormula(SOMME.SI('DATA OPPORTUNITY'!E2:E,unique(filter('DATA OPPORTUNITY'!E2:E,'DATA OPPORTUNITY'!B2:B>=B1,'DATA OPPORTUNITY'!B2:B<=B2)),'DATA OPPORTUNITY'!Z2:Z))),"")}
,A4,SI(B4="croissant",VRAI,FAUX))
It's really scary when you see that but it works. On the other hand, with the size of the formula, I told myself that I was probably doing it really badly and that there must be a much simpler solution.
To talk about the formula, it allows me to display each column correctly, with the right values and it takes 1 seconds to make the change with my dates.
I wanted to use a filtered view to do ascending sorting of the columns, but the filtered view doesn't work with this kind of formula. So I was able to use SORT which allows me thanks to the IF (SI in French) inside to choose which column to sort and in which direction.
My last problem, as you can see in the customer view screenshot, I see customers who are at 0. These are customers who have had no orders with us, just lost, dead or canceled projects. I cannot exclude them from my calculation, because I also need to know the number of lost projects with other clients. I just need at the end, after the calculation, not to show the customers who made $ 0 with us in income.
What do you think of the giant formula, does it sound optimal to you? And for customers with $ 0, how can I hide them?
Thank you very much for the time spent reading this long text and for the answers you can give me! :)
Thanks to Aresvik's comment which directed me to the right solution, I was able to solve all of my problems with this formula :
=QUERY(
QUERY(
'DATA OPPORTUNITY'!A2:Z,"select E, sum(W), max(W), sum(X), sum(Z), sum(W)/sum(X),
sum(X)/(sum(X)+sum(Y)+sum(Z))
where" &SI(F2<>""," E='"&F2&"' and "," ")& "B >= date '"&TEXTE(B1,"yyyy-mm-dd")&"'
and B <= date '"&TEXTE(B2,"yyyy-mm-dd")&"'
group by E label E'', sum(W)'', max(W)'', sum(X)'', sum(Z)'', sum(W)/sum(X)'',
sum(X)/(sum(X)+sum(Y)+sum(Z))''"
)
, "select * where Col2 <> 0 and Col2 is not null Order By
Col"&SIERREUR(EQUIV(D1,5:5,0),1)&SI(D2="A to Z"," asc"," desc")
)
The query in the query allows me to take into account the lost contracts in my calculation in the 1st query, but with the 2nd not to display the customers who have not reported income during the targeted period. Compared to the initial project I added max (W) which allows me to know the highest project with a client. I also added references to cells to know by which column we want to sort, and if it is increasing or decreasing. And finally, in the "where" a criterion which appears only if the cell is filled, allowing to display only one customer.