I have an invoicing summary and reporting spreadsheet and am trying to create a dynamic summary for each client (number of clients can change) where each invoice is listed per client, with a sum total for each client after the list of invoices. Originally, I had made it so the list of invoices for every client were listed first, and then all the totals at the end. This was done to simplify things. However, I would like to show the list of invoices per client, with their totals right after their respective lists, rather than all at the end.
i.e.
List of invoices for client 1
List of invoices for client 2
Totals for client 1
Totals for client 2
Grand totals for all clients
vs.
Client 1
List of invoices for client 1
Totals for client 1
Client 2
List of invoices for client 2
Totals for client 2
Grand totals for all clients summarized
Unfortunately, the only way I know how to do show it as the first example will result in a number of blank lines for non-existing clients.
List of clients are pulled in with:
=sort(UNIQUE(E6:E),1,true)
This unique formula is pulling in the client names from another dynamic query, so number of clients may change. So the summary in my query formula below has an array per line (showing 4 total as an example, could potentially be higher).
Query data, where Data!N7:N16 is the range from the above unique formula:
={query(Data!A5:I5,"select A,B,C,D,' ',F,G,H,I label ' ' ''");
IF(Data!N7<>"",
{{Data!N7,"","","","","","","",""};query(Data!A6:Y,"select A,B,C,D,' ',F,G,H,I where A is not null and E='"&Data!N7&"' label ' ' ''");query(Data!J6:Y,"select V,W,X,Y,O,J,K,L,M where N='"&Data!N7&"'");{"___________________________________________________________________________________________________________________________________________","","","","","","","",""}},
{"","","","","","","","",""});
IF(Data!N8<>"",
{{Data!N8,"","","","","","","",""};query(Data!A6:Y,"select A,B,C,D,' ',F,G,H,I where A is not null and E='"&Data!N8&"' label ' ' ''");query(Data!J6:Y,"select V,W,X,Y,O,J,K,L,M where N='"&Data!N8&"'");{"___________________________________________________________________________________________________________________________________________","","","","","","","",""}},
{"","","","","","","","",""});
IF(Data!N9<>"",
{{Data!N9,"","","","","","","",""};query(Data!A6:Y,"select A,B,C,D,' ',F,G,H,I where A is not null and E='"&Data!N9&"' label ' ' ''");query(Data!J6:Y,"select V,W,X,Y,O,J,K,L,M where N='"&Data!N9&"'");{"___________________________________________________________________________________________________________________________________________","","","","","","","",""}},
{"","","","","","","","",""});
IF(Data!N10<>"",
{{Data!N10,"","","","","","","",""};query(Data!A6:Y,"select A,B,C,D,' ',F,G,H,I where A is not null and E='"&Data!N10&"' label ' ' ''");query(Data!J6:Y,"select V,W,X,Y,O,J,K,L,M where N='"&Data!N10&"'");{"___________________________________________________________________________________________________________________________________________","","","","","","","",""}},
{"","","","","","","","",""});
{"","","","",Data!O6,Data!J6,Data!K6,Data!L6,Data!M6}}
Which results in:
Client 1
List of invoices for client 1
Totals for client 1
Client 2
List of invoices for client 2
Totals for client 2
BLANK LINE SINCE N9 IS BLANK
BLANK LINE SINCE N10 IS BLANK
Grand totals for all clients summarized
Is there a better way to accomplish this without having these blank lines if a client doesn't exist in that range for the unique clients?
I created a sample spreadsheet with some of my formulas here https://docs.google.com/spreadsheets/d/1T9QkPGLzBMYd7lLy5EWd7ar_z4j97wweIZ6LJoBd-AU/edit?usp=sharing
try like this:
={REDUCE(QUERY(Data!A5:I5, "select A,B,C,D,' ',F,G,H,I label ' '''"),
Data!N7:INDEX(Data!N:N, MATCH(, 0/(Data!N:N>""))),
LAMBDA(a, x, LET(e, SORT(IFERROR(SEQUENCE(1, 8)/0)), {a; {{{x, e};
QUERY(Data!A6:Y, "select A,B,C,D,' ',F,G,H,I where A is not null and E='"&x&"' label ' '''");
QUERY(Data!J6:Y, "select V,W,X,Y,O,J,K,L,M where N='"&x&"'");
{REPT("_", 139), e}}}}))); {"","","","",Data!O6,Data!J6,Data!K6,Data!L6,Data!M6}}
without Data!J:O
use:
=LET(r, {REDUCE(QUERY(Data!A5:I5, "select A,B,C,D,' ',F,G,H,I label ' '''"),
UNIQUE(TOCOL(Data!E6:E, 1)), LAMBDA(a, x, LET(e, SORT(IFERROR(SEQUENCE(1, 8)/0)), {a; {{{x, e};
LET(q, QUERY(Data!A6:I, "select A,B,C,D,' ',F,G,H,I where A is not null and E='"&x&"' label ' '''"),
{q; HSTACK(,,,,"Totals",BYCOL(CHOOSECOLS(q, 6,7,8,9), LAMBDA(x, QUERY(SUM(x), "format Col1'$0.00'", ))))});
{REPT("_", 139), e}}}})))}, {r; HSTACK(,,,,"Grand Totals", BYCOL(Data!F6:I, LAMBDA(x, SUM(x))))})
for fun you can even replace REPT("_", 139)
with:
REPT("_", SUM(IFERROR(NOW()/0), BYCOL(INDIRECT("A"&ROWS(A:A)&":I"&ROWS(A:A)), LAMBDA(x, CELL("width", x)))))
this modification will auto adjust underscores _
based on the actual width of your columns
=LET(r, {REDUCE({Data!A5:D5,"",Data!F5:I5}, UNIQUE(TOCOL(Data!E6:E, 1)),
LAMBDA(a, x, LET(e, HSTACK(,,,,,,,), {a; {{{x, e};
LET(q, FILTER({Data!A6:D, IFERROR(Data!A6:A/0), Data!F6:I}, Data!E6:E=x),
{q; HSTACK(,,,,"Totals", BYCOL(CHOOSECOLS(q, 6,7,8,9),
LAMBDA(x, QUERY(SUM(x), "format Col1'$0.00'", ))))});
{REPT("_", SUM(IFERROR(NOW()/0), BYCOL(INDIRECT("A"&ROWS(A:A)&":I"&ROWS(A:A)),
LAMBDA(x, CELL("width", x))))), e}}}})))}, {r;
HSTACK(,,,,"Grand Totals", BYCOL(Data!F6:I, LAMBDA(x, SUM(x))))})