## Conditional Sum On #N/A Text Values

Today’s post is inspired by a question which came up over at the MrExcel.com forums.

It’s pretty simple. In cells `A1:A2` I have the formula `="#N/A"` and in cells `A3:A4` I have the formula `=NA()`. Whilst the outputs of these formulas look similar, they are fundamentally different: `A1` and `A2` are text types whilst `A3` and `A4` are error types. The respective left and middle alignments in the cells hint at this.

The challenge is how to use `SUMIF()` to get a sum of the values in column `B` which correspond to the text `"#N/A"` values – and it isn’t quite as easy as you’d think. We want an answer of `3` but all of these formulas return `12`:

`=SUMIF(A1:A4,"#N/A",B1:B4)`

`=SUMIF(A1:A4,"=#N/A",B1:B4)`

`=SUMIF(A1:A4,A1,B1:B4)`

It appears that, under the hood, `SUMIF()` coerces each of these `criteria` arguments from text types into error types before it does its comparisons. Before you read on have a go at finding the solution.

After a bit of playing around, here’s the best I could come up with in terms of pure `SUMIF()` formulae:

`=SUMIF(A1:A4,"*#N/A",B1:B4)`

Or variations of:

`=SUMIF(A1:A4,"?N/A",B1:B4)`

`=SUMIF(A1:A4,"#?/A",B1:B4)`

etc…

The `*` and `?` wildcards convince `SUMIF() `that we want it to work with text types. If you’re not familiar with using wildcards in `SUMIF()` then check out this blog post I wrote a couple of years ago.

Of course, these formulae aren’t foolproof. For example, if `A1` contained the formula `="abc#N/A"` then it would be included in the `*` wildcard sum. We only want pure `"#N/A"` values to be included. To cater for those somewhat unlikely scenarios I ended up tweaking the formula to a `SUMIFS()`:

`=SUMIFS(B1:B4,A1:A4,"?N/A",A1:A4,"#???")`

I think that one’s solid. Another option, which I’m not nearly as keen on, is this array formula (CTRL + SHIFT + ENTER):

`=SUM((IFNA(A1:A4,"")="#N/A")*B1:B4)`

So, can you improve on the `SUMIFS()` formula or, even better, find a robust way of doing it with `SUMIF()`?