title: Conditionally highlighting totals that don't match a dynamically summed range
body: <p><strong>Objective:</strong></p>
  
  <p>I am looking to use conditional formatting to highlight cells in rows that are not equal to the sum of a dynamic range. </p>
  
  <p><strong>Problem:</strong></p>
  
  <p>While the formula I have created seems to work when pasted into cells, it does not give the same results when entered as a conditional formula. </p>
  
  <p><strong>Example:</strong></p>
  
  <p>Here is a space delimited example to be pasted into "A1":</p>
  
  <pre><code>Allo    d1  d2  d3  d4  d5
  Total   10  10  10  10  10
  A   9   9   10  10  9
  B   0   0   0   0   0
  C   0   1   0   0   0
  Total   12  12  12  12  12
  B   0   5   0   3   4
  C   12  7   8   8   8
  Total   12  12  12  12  12
  A   0   0   0   0   0
  B   0   0   0   0   0
  C   0   5   0   3   4
  D   12  7   8   8   8
  </code></pre>
  
  <p>I wrote this formula which shows TRUEs and FALSEs correctly when pasted into "H2" and dragged to the right and down to "L13." When I apply this formula to the data range "B2:F13" it does not mimic what I'd expect.</p>
  
  <pre><code>=IF($A2="TOTAL", B2 &lt;&gt; SUM(INDIRECT(ADDRESS(ROW(B3),COLUMN(B3),4)&amp;":"&amp;ADDRESS(ROW(B2)+IFERROR(MATCH("TOTAL",$A3:$A$13,0)-1,ROW($A$13)-ROW($A2)),COLUMN(B2),4))))
  </code></pre>
  
  <p>Below you can see the formula broken out in a more easy to read way. Is my formula flawed/ How can I accomplish what I am trying to do? I appreciate your thoughts.</p>
  
  <pre><code>=IF($A2="TOTAL", 
      B2 &lt;&gt; SUM( 
                INDIRECT( ADDRESS( ROW(B3), 
                                   COLUMN(B3), 
                                   4) &amp;":"&amp; 
                          ADDRESS( ROW(B2) + IFERROR( 
                                                     MATCH( "TOTAL", $A3:$A$13, 0)-1, 
                                                     ROW($A$13)-ROW($A2)),
                                   COLUMN(B2),4))))
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/Mh9We.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Mh9We.png" alt="enter image description here"></a></p>