Problem with SUMIF Formula?

Copper Contributor

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?




1 Reply


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:







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.