Today’s post is inspired by a question which came up over at the MrExcel.com forums.
Here’s my spreadsheet:
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:
A2 are text types whilst
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
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
Or variations of:
? 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
I think that one’s solid. Another option, which I’m not nearly as keen on, is this array formula (CTRL + SHIFT + ENTER):
So, can you improve on the
SUMIFS() formula or, even better, find a robust way of doing it with