title: Protecting TIMEVALUE usage inside SUMPRODUCT from input errors
body: <p>First question here, thanks in advance for your patience and assistance!</p>
  
  <p>I have a spreadsheet that contains course types (column A, e.g. "Lec" or "Dis"), enrollment numbers (column B, as integers), and session times (column C, string time interval e.g. "10:00AM - 01:00PM"). </p>
  
  <p>I need to sum the enrollment numbers for all classes of a certain type (say "Lec") that end before a certain time (say 3:00 PM). The following code accomplishes that:</p>
  
  <pre><code>=SUMPRODUCT(--((A2:A8="Lec")), --(TIMEVALUE(MID(B2:B8,11,5)&amp;" "&amp;MID(B2:B8,16,2))&lt;=TIMEVALUE("03:00 PM")), C2:C8)
  </code></pre>
  
  <p>The problem is that some of the session times may be blank, which renders a "#VALUE!" error. As best I can tell this is because TIMEVALUE is unable to accept an empty cell as input, so whenever that occurs it breaks the whole thing. I therefore need in this case for the formula to ignore the row and not progress to the various other evaluations.</p>
  
  <p>I have attempted to protect the TIMEVALUE content behind various conditional statements, e.g. only considering that row if the time session length is correct (length=17). I have not been able to do this successfully, however. Nor have my internet searches found a similar-enough situation to use for help.</p>
  
  <p>Because of the circumstances around this project, I am strongly interested in formula solutions before considering things like changing the overall spreadsheet format or adding dummy columns for computations. That said, I welcome any and all suggestions!</p>