title: Adding Total Rows to stacked Google Query Tables
body: <p>I have this huge but beautiful formula which was cleverly made by esteemed Stackoverflow member @player0, that works really well.</p>
  <ul>
  <li><p>I need to add Total rows to the 3 generated query tables. As i donot completely understand the formula, i am having difficulty in adding Total rows. Each Total row will Sum the values in the Columns, except the last Table which will take an average of the percentages in the columns.</p>
  </li>
  <li><p>Also if the data is not available for a particular year, it results in a #VALUE error. I need to show some message or a <code>Blank</code> if data is not available e.g. <code>Data not available for this year</code>.</p>
  </li>
  </ul>
  <p>Here is an <a href="https://docs.google.com/spreadsheets/d/1gOvcpeTNxwwqu6-jFQJDqPD1PlX1oL2Z8cOpjNHB0Q0/edit?usp=sharing" rel="nofollow noreferrer">FTRTable</a> with Edit access.</p>
  <p>I worked up a formula for adding Total Rows for the 1st 2 tables but don't know where to insert it e.g.:</p>
  <pre><code>{TRANSPOSE(QUERY('Study Report'!$A:$T,&quot;SELECT COUNT(B) WHERE YEAR(S) = &quot;&amp; $C$1 &amp;&quot; AND O != 'NO - all fine' AND L != 'Cancelled' GROUP BY S LABEL COUNT(B) 'TOTAL'&quot;,0))}
  </code></pre>
  <p>and</p>
  <pre><code>{TRANSPOSE(QUERY('Study Report'!$A:$T,&quot;SELECT COUNT(B) WHERE YEAR(S) = &quot;&amp; $C$1 &amp;&quot; AND L != 'Cancelled' GROUP BY S LABEL COUNT(B) 'TOTAL'&quot;,0))}
  </code></pre>
  <p><strong>------------EDIT:------------</strong></p>
  <p>Updated the formula under <code>Clubbed Tables</code> to Add <code>Totals</code> for <code>ALL Tables</code>. It works well in my production sheet, but not in FTR Table spreadsheet. <code>The only thing remaining:</code></p>
  <ul>
  <li>For particular years, where data may not be available, how do i remove the <code>#VALUE</code> error and show a Blank or some other text?</li>
  </ul>
  <p><strong>Updated Formula where all 3 Tables having their Totals rows:</strong></p>
  <pre><code>=ARRAYFORMULA(IF(C1=&quot;&quot;, &quot;select Year in C1&quot;, {TRANSPOSE(QUERY(TRANSPOSE(QUERY(IFNA(
   HLOOKUP(INDEX(TRANSPOSE(QUERY('Study Report'!$A:$T, 
   &quot;select S,count(B) 
    where S is not null 
      and year(S)=&quot;&amp;C1&amp;&quot; 
      and L!='Cancelled' 
      and O!='NO - all fine' 
  
    group by S 
    pivot N 
    label S 'NO OF ISSUES'&quot;)), 1), TRANSPOSE(QUERY({'Study Report'!$A:$T}, 
   &quot;select Col19,count(Col2) 
    where Col19 is not null
      and year(Col19)=&quot;&amp;C1&amp;&quot; 
      and Col12!='Cancelled' 
      and Col15 != 'NO - all fine' 
    group by Col19 
    pivot Col14 
    label Col19 'NO OF ISSUES'&quot;)), ROW(A:A), 0)), 
   &quot;where Col1 &lt;&gt; '#REF!'&quot;)), 
   &quot; format Col1 'yyyy-mmm'&quot;)); TRANSPOSE(QUERY('Study Report'!$A:$T,&quot;SELECT COUNT(B) WHERE YEAR(S) = &quot;&amp; $C$1 &amp;&quot; AND O != 'NO - all fine' AND L != 'Cancelled' GROUP BY S LABEL COUNT(B) 'TOTAL'&quot;,0));
   SPLIT(REPT(&quot; ♠&quot;, COLUMNS(TRANSPOSE(QUERY('Study Report'!$A:$T, 
   &quot;select S,count(B) 
    where S is not null
      and year(S)=&quot;&amp;C1&amp;&quot; 
      and L!='Cancelled' 
    group by S 
    pivot N 
    label S 'TOTAL OPPORTUNITIES'&quot;)))), &quot;♠&quot;); 
   TRANSPOSE(QUERY('Study Report'!$A:$T, 
   &quot;select S,count(B) 
    where S is not null
      and year(S)=&quot;&amp;C1&amp;&quot; 
      and L!='Cancelled' 
    group by S 
    pivot N 
    label S 'TOTAL OPPORTUNITIES'&quot;)); TRANSPOSE(QUERY('Study Report'!$A:$T,&quot;SELECT COUNT(B) WHERE YEAR(S) = &quot;&amp; $C$1 &amp;&quot; AND L != 'Cancelled' GROUP BY S LABEL COUNT(B) 'TOTAL'&quot;,0));
   SPLIT(REPT(&quot; ♠&quot;, COLUMNS(TRANSPOSE(QUERY('Study Report'!$A:$T, 
   &quot;select S,count(B) 
    where S is not null
      and year(S)=&quot;&amp;C1&amp;&quot; 
      and L!='Cancelled' 
    group by S 
    pivot N 
    label S 'TOTAL OPPORTUNITIES'&quot;)))), &quot;♠&quot;); 
  
   REGEXREPLACE(TO_TEXT(QUERY(QUERY({QUERY(
   {'Study Report'!S1:S&amp;&quot;♦&quot;&amp;'Study Report'!N1:N, 'Study Report'!B1:T, 'Study Report'!S1:S&amp;
   IF('Study Report'!S1:S=&quot;&quot;,,&quot;♥&quot;&amp;TEXT('Study Report'!S1:S, &quot;yyyy-mmm&quot;))}, 
   &quot;select Col1,Col19,Col14,Col21,count(Col2) where Col19 is not null and year(Col19)=&quot;&amp;C1&amp;&quot; and Col12 != 'Cancelled' group by Col1,Col19,Col14,Col21&quot;),
   IFNA(VLOOKUP(INDEX(QUERY({'Study Report'!S1:S&amp;&quot;♦&quot;&amp;'Study Report'!N1:N, 'Study Report'!B1:T}, 
   &quot;select Col1,Col19,Col14,count(Col2) where Col19 is not null and year(Col19)=&quot;&amp;C1&amp;&quot; and Col12 != 'Cancelled' group by Col1,Col19,Col14&quot;),,1), 
   QUERY({'Study Report'!S1:S&amp;&quot;♦&quot;&amp;'Study Report'!N1:N, 'Study Report'!B1:T}, 
   &quot;select Col1,count(Col2) where Col19 is not null and year(Col19)=&quot;&amp;C1&amp;&quot; and Col12 != 'Cancelled' and Col15!= 'NO - all fine' group by Col1&quot;), 2, 0))}, 
   &quot;select Col4,Col3,Col6/Col5&quot;), 
   &quot;select Col2,max(Col3) group by Col2 pivot Col1 label Col2'FTR%' format max(Col3)'#.00%'&quot;)), &quot;(\d+♥)&quot;, );
  
  regexreplace(to_text(transpose(query(query(QUERY({QUERY(
   {'Study Report'!S1:S&amp;&quot;♦&quot;&amp;'Study Report'!N1:N, 'Study Report'!B1:T, 'Study Report'!S1:S&amp;
   IF('Study Report'!S1:S=&quot;&quot;,,&quot;♥&quot;&amp;TEXT('Study Report'!S1:S, &quot;yyyy-mmm&quot;))}, 
   &quot;select Col1,Col19,Col14,Col21,count(Col2) where Col19 is not null and year(Col19)=&quot;&amp;C1&amp;&quot; and Col12 != 'Cancelled' group by Col1,Col19,Col14,Col21&quot;),
   IFNA(VLOOKUP(INDEX(QUERY({'Study Report'!S1:S&amp;&quot;♦&quot;&amp;'Study Report'!N1:N, 'Study Report'!B1:T}, 
   &quot;select Col1,Col19,Col14,count(Col2) where Col19 is not null and year(Col19)=&quot;&amp;C1&amp;&quot; and Col12 != 'Cancelled' group by Col1,Col19,Col14&quot;),,1), 
   QUERY({'Study Report'!S1:S&amp;&quot;♦&quot;&amp;'Study Report'!N1:N, 'Study Report'!B1:T}, 
   &quot;select Col1,count(Col2) where Col19 is not null and year(Col19)=&quot;&amp;C1&amp;&quot; and Col12 != 'Cancelled' and Col15!= 'NO - all fine' group by Col1&quot;), 2, 0))}, 
   &quot;select Col4,Col3,Col6/Col5&quot;),&quot;select Col1, avg(Col3) where Col1 is not null group by Col1 format avg(Col3) '0.00%'&quot;),&quot;Select Col2 label Col2 'TOTAL'&quot;))),&quot;(\d+♥)&quot;,)
  }))
  </code></pre>