To answer your question, you can turn the source data table above to the result table above with this formula:
=arrayformula(
query(
query(
{ A1:A, if(regexmatch(B1:B, "(?i)Credit"), -C1:C, C1:C), B1:B },
"select Col1, sum(Col2)
where Col1 is not null
group by Col1",
1
),
"select Col1, 'Debit', Col2
where Col2 <> 0
label 'Debit' 'Transaction' ",
1
)
)
But your sample spreadsheet tells that you ultimately need to consider labels 1% Debit
, 2% Debit
and 5% Debit
each separately. You can do that with an array formula that massages the data based on whether a row is Debit or Credit, and aggregate the massaged data with a query()
that uses a group by
clause. To weed out rows with a zero result, and create columns for each of 1% Debit
, 2% Debit
and 5% Debit
separately, wrap the query()
in another query()
that uses a pivot
clause, like this:
=arrayformula(
query(
query(
{ A1:A, if(regexmatch(E1:E, "(?i)Credit"), -G1:G, G1:G), E1:E },
"select Col1, sum(Col2), Col3
where Col1 is not null
group by Col1, Col3",
1
),
"select Col1, sum(Col2)
where Col2 <> 0
and lower(Col3) contains 'debit'
group by Col1
pivot Col3",
1
)
)
To calculate per-flag totals, use this:
=arrayformula( iferror( 1 / ( 1 / sumif( if(column(K7:N29), row(K7:N29)), row(K7:N29), K7:N29 ) ) ) )
See cells K6
and J7
in your sample spreadsheet.
Note that you can use open-ended references like A1:A
that will evaluate rows all the way to the bottom of the sheet only when there are no more results or data below the result table. To limit it to some fixed number of rows, use A1:A500
and so on.