title: Excel COUNTIF with conditional formatting
body: <p>I have one sheet (Attendance Sheet) where I have entered attendance of employees manually.</p>
  
  <p>Now there are a few conditional formattings applied on the sheet. For example:</p>
  
  <ol>
  <li>If someone enters the office after 9:30AM a yellow color will highlight the cell.</li>
  <li>If someone is absent a "A" in the cell will highlight the cell as red color.</li>
  <li>If someone is on leave a "Leave" in the cell will highlight the cell as green color.
  and so on...</li>
  </ol>
  
  <p>This all works fine and flawlessly.</p>
  
  <p>Now to the issue.</p>
  
  <p>I have manually set the colors in the "Legend" area in the sheet for reference for the formula, countif.</p>
  
  <p><a href="https://imgur.com/a/FLsAV" rel="nofollow noreferrer">https://imgur.com/a/FLsAV</a></p>
  
  <p>When I want to use COUNTIF to calculate the total number of days the employee was late, absent or on leave, I select a cell, enter the formula, for example I want to count the number of days the employee was Late, I use, =COUNTIF(H1:H30,A4)... (Where A4 is the cell of the Yellow color and H1:H30 is the range).</p>
  
  <p>and the result returns "0" while I have Yellow cells highlighted via conditional formatting. I have tried working on this several hours via google and YouTube but to no avail.</p>
  
  <p>Please help.</p>