Hey all! In my example below, I am trying to sum up 4 boxes. They contain N/A, 2, 1, and 3. I tried using the SUMIF formula to ignore (or use 0) when the cell contains N/A. However, it is returning #VALUE! when I use this formula. Is there something I'm doing wrong or a better way to do this?
The error is coming from the way the range was provided. This is how Excel sees it in Function Arguments:
In your sheet from the screen cap, it shows E10 contains "N/A" which Excel interprets as a text string. SUM will ignore this text and still provide a total:
=SUM(E10,I10,M10,Q10)
If you encounter a #N/A, that's another matter. That will cause SUM to return a #VALUE! error. To sum and disregard such an error, you could use AGGREGATE.