title: Excel: 'Sumifs' ignoring #n/a
body: <p>I'm trying to sum some values (both positive and negative) in a column, but it won't sum as there are #n/a values. Is there any way to get around this? Please see the example below:</p>
  <p><a href="https://i.stack.imgur.com/1iUW1.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/1iUW1.png" alt="enter image description here" /></a></p>
  <p>Each city has lower areas that have income,crime &amp; unemployment scores (either positive or negative).
  My goal is to get city-wide income, crime and unemployment scores by summing lower areas' scores.
  I'm using 'sumifs' in G2 as in =SUMIFS(C$2:C$10,$A$2:$A$10,$A2) and then dragging it to I10. In this toy example, there are only 10 rows, but my data have 1 million rows, so I cannot really do dragging. Any suggestions on that would also be helpful!</p>
  <p>But, most importantly my problem is that I cannot use 'sumifs' due to the #N/A values. I want to ignore them.</p>
  <p>Or simply replacing all the #N/A values with 0 in the 1 million rows would also be an option.</p>
  <p>p.s. I have done some research on previous similar questions, but they seem to use different 'sumifs' formulae...</p>